SAS EG append new data and overwrite already existing rows-CodePudding

My goal is to update this table called DAGLIGEKORREKTIONER_lib_xlsx:

Here i a sample of my data. Don't mind the cmd prompt it is only to cover sensitive data.

I then have a table with a similar structure called DAGLIGEKORREKTIONER:

But how do I append the table DAGLIGEKORREKTIONER_lib_xlsx with DAGLIGEKORREKTIONER. And if DAGLIGEKORREKTIONER_lib_xlsx contains rows with the same "approval_text" as DAGLIGEKORREKTIONER the rows in DAGLIGEKORREKTIONER_lib_xlsx gets deleted and the rows from DAGLIGEKORREKTIONER is appended.

I hope you can point me in the right direction

CodePudding user response：

Here is how you can do it in a two-step method if your table is small.

First, create a temporary table of DAGLIGEKORREKTIONER_lib_xlsx that excludes matching values of approval_text. This is similar to deleting matching values of approval_text.

proc sql;
    create table temp as
        select *
        from DAGLIGEKORREKTIONER_lib_xlsx 
        where approval_text NOT IN(select approval_text from DAGLIGEKORREKTIONER)
    ;
quit;

Then set the two tables together. This will append all the new and updated values to the original table.

/* Append the updated and new values */
data DAGLIGEKORREKTIONER_lib_xlsx;
    set temp
        DAGLIGEKORREKTIONER
    ;
run;

There are more sophisticated ways to do this if you have big data, but this is a very easy way to do updates to old data for small tables. The reason we're doing it this way is because approval_text is not unique. If there are a differing number of values of approval_text, we want to remove all of the old rows and append these new rows which could include more or fewer values.