topic Re: updating master table in SAS Procedures

updating master table

Tal — Thu, 11 May 2017 23:44:08 GMT

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

Re: updating master table

ballardw — Fri, 12 May 2017 20:17:22 GMT

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.

Re: updating master table

BaalaRaaji — Wed, 30 May 2018 00:08:45 GMT

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,

Re: updating master table

PeterClemmensen — Wed, 30 May 2018 06:51:35 GMT

You should open a new thred instead of answering a year-old thread with a new question 🙂

Regards.