BookmarkSubscribeRSS Feed
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

3 REPLIES 3
ballardw
Super User

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.

BaalaRaaji
Quartz | Level 8

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,

 

 

PeterClemmensen
Tourmaline | Level 20

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

 

Regards.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 884 views
  • 0 likes
  • 4 in conversation