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
Advertisement
Answer
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.