CODEWITHSUNDEEP
replacemergeappendsasupdates
kstats9pt3
kstats9pt3

Reputation: 873

SAS Modify and/or Replace Master Data with Updated Data

I have two data sets -- a master data set that needs to be periodically updated with the latest data that is contained in a separate data set. I've been playing around with MODIFY statements but I'm having trouble completely replacing groups of data. The code below creates two sample data sets:

data new;
    input year dqtr age yearq;
    datalines;
    2011    4   43  20114
    2011    4   25  20114
    2013    1   52  20131
    2013    1   45  20131
    2013    2   72  20132
    2013    2   43  20132
    2013    3   93  20133
    2013    3   97  20133
    2013    4   23  20134
    2013    4   37  20134
;
run;

data master;
    input year dqtr age yearq;
    datalines;
    2011    3   94  20113
    2011    3   76  20113
    2011    3   56  20113
    2011    4   43  20114
    2011    4   62  20114
    2011    4   73  20114
;
run;

The variable YEARQ is a concatenated variable of YEAR and DQTR. If the YEARQ combination is not in the master data set, it should be appended. If it is within the master data set, then it should replace the entire group. For example, only YEARQ=20114 should be in the final data set. The 3 original 20114 obs should be replaced with the 2 new 20114 obs. The code below almost does what I want, but entire groups of YEARQ are not replaced (it only replaces the first however many observations of that combination found):

data master(index=(yearq)); 
    set master;
run;

data master;
    set new;
    modify master key=yearq;
   if _iorc_ =0 then replace;
   else do; 
         _error_=0;       
         _iorc_=0;  
      output;  
      end;
run;

Upvotes: 2

Views: 1305

Answers (1)

in_user
in_user

Reputation: 1958

As I see your update process, essentially what you are trying to do is following

  1. If your new dataset contains an existing yearq value then your are updating it in the master dataset i.e. deleting the all the common yearq from master and appending the new yearq from new to master
  2. Also you are appending any new yearq to the master dataset.

Extracting common yearq

proc sql;
create table common as select distinct(a.yearq) from master as a, new as b
where a.yearq=b.yearq;
quit;

Deleting common yearq from master

proc sql;
create table temp as select * from master where yearq not in(select yearq from common);
quit;

Appending new dataset to master dataset

data master;
set temp
    new;
    run;

Upvotes: 1

Related Questions