DATA Step, Macro, Functions and more

Merging two dataset with a key

Reply
Occasional Contributor cm2
Occasional Contributor
Posts: 6

Merging two dataset with a key

Hi,

I'm merging two dataset, previous and current with a key in my by statement.  I'd like to know which ones are new and which are changed from the previous dataset.  I can get the new records from the IN= option, but how can I find which are changed?

 

key=compress(usubjid||visit||visdat||vissttim||qstestcd||qsorres);

data incurr(drop=date siteidp usubjidp catp testcdp resultp visitp visdatep visstp)

inprev(drop=key date siteidc usubjidc catc testcdc resultc visitc visdatec visstc)

inboth;

merge prevdata(in=inprev) currdata(in=incurr);

by key;

if incurr and ^ inprev then output incurr;

else if inprev and ^ incurr then output inprev;

if inprev and incurr then output inboth;

run;

Super User
Posts: 5,498

Re: Merging two dataset with a key

It's extremely difficult to do this in the same DATA step that merges.  But it's easier to do after the fact. 

 

Extract a subset of PREVDATA, taking just those KEY values that appear in INBOTH.  It looks like you know how to use IN= variables to subset, so that step should be relatively easy.  (Just make sure that KEY is the only variable that you bring in from INBOTH when you subset.)  Then run a PROC COMPARE, to compare that subset with INBOTH.

Occasional Contributor cm2
Occasional Contributor
Posts: 6

Re: Merging two dataset with a key

Posted in reply to Astounding

Hi,

I first did the subset of prevdata with inboth and it gave me the same amount of obs that was in prevdata.  Then I did the proc compare and it didn't show me which were changed from prevdata.  What am I doing wrong?

 

data compare;

merge inprev(in=prev) inboth(in=both);

by key;

if prev;

run;

 

sorts by key...

 

proc compare base=compare compare=inboth;

run;

 

Value Comparison Results for Variables

__________________________________________________________

                  || Base Value                             Compare Value

Obs           || key                                                  key

________ || ___________________+ ___________________+

||

1 || 01001001BASELINE(V3W             01001001BASELINE(V3W

2 || 01001001BASELINE(V3W             01001001BASELINE(V3W

3 || 01001001BASELINE(V3W             01001001BASELINE(V3W

4 || 01001001RUN-IN(V2W-3              01001001BASELINE(V3W

5 || 01001001RUN-IN(V2W-3              01001001BASELINE(V3W

6 || 01001001RUN-IN(V2W-3              01001001BASELINE(V3W

 

Super User
Posts: 5,498

Re: Merging two dataset with a key

In merging, the values from INBOTH are always being used for all variables.  They overwrite any values from INPREV.  To overcome that, the MERGE statement should look like this:

 

merge inprev(in=prev) inboth(in=both keep=key);

Contributor
Posts: 23

Re: Merging two dataset with a key

*Create an indicator of origin of 'key';
data changes;
merge prevdata(in=inprev) currdata(in=incurr);
by key;
length change_flag $ 24;
if incurr and inprev then change_flag = "Both Files --- No Change";
else if inprev then change_flag = "Previous File Only";
else if incurr then change_flag = "Current File Only";
run;

*Find frequencies of origins of 'key';
proc freq data = changes;
tables change_flag;
run;
Frequent Contributor
Posts: 136

Re: Merging two dataset with a key

[ Edited ]

Hi,

 

Are you trying to update your master table with new values from the transaction table. Then UPDATE statement maybe the best way and look my example code :

 

data one;
infile datalines ;
input val1 $ val2 $;
valx=val2;
datalines;
A AA
B BB
C CC
D DD
E EE
;
RUN;
data two;
infile datalines ;
input val1 $ val2 $;
datalines;
A AA1
B BB
C CC
D DD1
E EE
;
RUN;
proc sort data=one;
by val1;
proc sort data=two;
by val1; data compare_one_two; update one two; by val1; IF val2 ^=valx then status="Updated"; run;

Hope this helpsSmiley Happy

 

Thanks,

Surya

Thanks,
Suryakiran
Contributor
Posts: 23

Re: Merging two dataset with a key

Posted in reply to SuryaKiran

The poster was asking about which key (merging) variables had changed. In your example, no key variables have changed, only the values associated with them.

Regular Contributor
Posts: 227

Re: Merging two dataset with a key

I wrote a paper at the end of the previous century

with a macro to show the differences.

 

SUGI 23: %COMPARWS: Compare with Summary: A Macro Using Proc Compare
 

www2.sas.com/proceedings/sugi23/Posters/p170.pdf

 
sas.community.org has a zip with the programs
 
 
The above assumes that both data sets have the same keys.
 
I wrote another macro which identifies the adds, changes, and deletes.
 
 
Ron Fehd 
Ask a Question
Discussion stats
  • 7 replies
  • 153 views
  • 0 likes
  • 5 in conversation