I want to append a master file with update file based on index (Company Year)
File Master
Company Year Revenue
A 1991 100
A 1992 200
A 1993 300
C 1991 100
File update
Company Year Revenue
A 1991 100
A 1993 300
B 1991 50
B 1992 100
C 1991 100
Output
Company Year Revenue
A 1991 100
A 1992 200
A 1993 300
B 1991 50
B 1992 100
C 1991 100
I am using this code - but it does not remove duplicates
proc sql;
create table want as
select * from Master
union all corr
( select * from update
except all
select * from master)
;
run;
Does it have to be an SQL solution?
It is not necessary to have a SQL ,but I thought SQL could be cleaner and simpler.
You could use MODIFY.
SAS contains a relatively simple tool to make this happen. Assuming both data sets are sorted:
data want;
update master changes;
by company year;
run;
This tool does build in certain assumptions. You would probably hope that all of these conditions would be true, anyway ...
The MASTER data set is not permitted to have more than one record for any company/year combination. While the CHANGES data set is permitted to have multiple records, the final result will contain only one record per company/year combination after all the changes have been applied. Any missing values (such as for REVENUE) in the CHANGES data set will be ignored. But if CHANGES contains a REVENUE value, that value will appear in the final result whether or not MASTER already contains a REVENUE value for that company/year.
Good luck.
Thanks Astounding, but I forgot to mention that my master file should not get overwritten by the transaction file if the record exists in the master file. So SAS update command may not help.
That's actually easy to overcome, as long as the transaction file never contains multiple records for a COMPANY/YEAR. (Even that can be overcome but requires either an additional step or a more complex program such as data_null's suggestion.) Just switch the files:
data want;
update changes master;
by company year;
run;
Now any non-missing values in the MASTER file replace the values in the transaction file.
that's clever!!! Perfect, it works in the current scenario.
Thanks
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.