Help using Base SAS procedures

updating master table

Reply
Super Contributor
Super Contributor
Posts: 440

updating master table

[ Edited ]

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

Super User
Posts: 10,454

Re: updating master table

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.

Ask a Question
Discussion stats
  • 1 reply
  • 99 views
  • 0 likes
  • 2 in conversation