hi,
i have a master dataset which gets updated by a daily file.
a) if the record from the daily table exists in the master then nothing to be done
b) if new record then simply append it to the master table
c) if the client from the daily exists in the master table but with changes then his new record from the daily gets appended to the master table and the date field of his previous record gets updated with the date of the update occured
daily: current master: master table after appending: master table with date field updated:
id x y id x y date id x y date id x y date
--------- ------------------------------ -------------------------------- ----------------------------------------
100 1 2 100 1 2 100 1 2 100 1 2
101 3 4 101 2 2 13/04/2016 101 2 2 13/04/2016 101 2 2 13/04/2016
102 5 6 101 3 4 101 3 4 101 3 4
103 3 3 102 1 1 13/04/2016 103 3 3 103 3 3
102 1 3 15/04/2016 102 1 1 13/04/2016 102 1 1 13/04/2016
102 2 3 102 1 3 15/04/2016 102 1 3 15/04/2016
102 2 3 102 2 3 12/5/2017
102 5 6 102 5 6
updating the DATE field is what troubles me. Tried first.id and last.id,tried merging..... nothing worked.
Any hints pleasE?
Thx
This will get as far at the master appended for the provided example:
data work.daily; input id x y ; datalines; 100 1 2 101 3 4 102 5 6 103 3 3 ; run; data work.master; infile datalines missover ; input id x y date :ddmmyy10.; format date ddmmyy10.; datalines; 100 1 2 101 2 2 13/04/2016 101 3 4 102 1 1 13/04/2016 102 1 3 15/04/2016 102 2 3 ; run; proc sort data=work.daily; by id x y; run; data work.masterappended; update work.master work.daily; by id x y; run;
Note the first two datasteps provide minimum data to test code with.
However your variables X and Y apparently have a role somewhere in determining "the date field of his previous record gets updated with the date of the update occured" because as stated your example master should not have dates on both of these records:
102 1 1 13/04/2016
102 1 3 15/04/2016
because the apparent previous on 13/04/2016 would have been update with the second by the "client" (have to assume this means the variable ID)
I am assuming the date on this row
102 2 3 12/5/2017
represents the date of the daily file transaction. Is that correct? If so you should provide some information such as "the example daily file is from 12/05/2017 and that is the date appended.
Also your example ID 103 did not exist at all in the master. Should that record get a transaction date as the very first one? Otherwise your rule for updating going forward doesn't have anything to replace/update or what ever.
Hi THERE
Can you send me the code that you use to update the master dataset based on below 2 conditions.
I have a master dataset which gets updated by a daily file.
a) if the record from the daily table exists in the master then nothing to be done
b) if new record then simply append it to the master table
I have code to update it daily but sometimes it doesnt work properly..
Below is my code..
proc sql;
select count(*) into :new_nobs from old;
select count(*) into :prev_nobs from rs.new;
quit;
%put &new_nobs.;
%put &prev_nobs.;
data udrsa.test;
x=1;
run;
%macro load_dataset;
%global text;
%if &new_nobs. >= &prev_nobs. %then
%do;
Proc sql;
drop table rs.new;
quit;
data rs.new( fastload= yes sessions=4);
set old;
run;
%let text ="Status Green. Similar or Higher records than yesterday";
%end;
%else %do;
%let text = "Status Red. Lower Records than yesterday, Process has not been refreshed";
%end;
%mend load_dataset;
%load_dataset;
%put &text.;
Appreciate your reply..thanks
Regards,
You should open a new thred instead of answering a year-old thread with a new question 🙂
Regards.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.