Hi:
Here's a concrete example to illustrate the use of the IN= option on the MERGE statement. The IN= option creates a temporary variable that holds either a 1 or a 0 value depending on whether the dataset contributes any information to the current Program Data Vector (PDV) being populated by the MERGE (the output data comes from the PDV). Your DATA step will create a PDV area for every dataset being created.
The nice thing about using IN= is that you can do multiple tests and multiple outputs based on your conditions. For example, Consider this data. WORK.ONE is the "name" file. It has COMPANY, JOB, YEAR and NAME. WORK.TWO is the "amount" file. It has COMPANY, JOB and AMOUNT:
data one;
infile datalines;
input company $ job $ year name $;
return;
datalines;
aa job1 2012 Alan
aa job4 2012 Alexa
bb job1 2012 Bob
cc job1 2012 Carla
dd job1 2012 Dana
dd job9 2012 Dave
ee job1 2012 Edith
;
run;
data two;
infile datalines;
input company $ job $ amount ;
return;
datalines;
aa job1 101
aa job2 102
aa job3 103
bb job1 201
cc job2 302
cc job3 303
dd job1 401
dd job9 409
ee job1 501
ff job1 601
;
run;
There are matches and non-matches and multiple jobs in both files. Merging on COMPANY and JOB gives me a unique combo of variables for the MERGE. However, if I do a simple MERGE, with only a BY, I will probably get undesirable results because the default is for all the matches and nonmatches to be put into the output dataset:
Given the data, I have some issues. I do have matches, but they are matches for a variety of jobs. What if I only want to get matches for the rows with JOB1? Well, that's pretty easy to do, but what about row #6 for Carla, she was in my WORK.ONE dataset, but there wasn't a match for her row in WORK.TWO - where do I want her row, in with matches or in with non-matches? Or do I need to go back and fix the data?
Let's look at the first use of a MERGE with IN=, where I save the values of the temporary variables into the output dataset, so I can examine them:
And, if I didn't want any further filter on JOB or YEAR, then this might be OK. But what if I DO want to separate the observations into one set of files where JOB=JOB1 and another set of files where JOB NE JOB1??? Then I have to modify the logic in the program and output to more datasets:
data match_both match_job1 match_not_job1
name_only name_job1 name_not_job1
amount_file_only amount_for_job1 amount_not_job1;
merge one(in=in_name) two(in=in_amt);
by company job;
if in_name=1 and in_amt=1 then do;
output match_both;
if job='job1' then output match_job1;
else if job ne 'job1' then output match_not_job1;
end;
else if in_name=1 and in_amt=0 then do;
output name_only;
if job='job1' then output name_job1;
else if job ne 'job1' then output name_not_job1;
end;
if in_name=0 and in_amt=1 then do;
output amount_file_only;
if job = 'job1' then output amount_for_job1;
else if job ne 'job1' then output amount_not_job1;
end;
run;
proc print data=match_both;
title '1) Match on Company and Job';
title2 'Without filter on JOB';
run;
proc print data=name_only;
title '2) Have name, but no amount';
title2 'Without filter on JOB';
run;
proc print data=amount_file_only;
title '3) Have amount, but not in name file';
title2 'Without filter on JOB';
run;
proc print data=match_job1;
title '4) Match on Company and Job';
title2 'Only filter JOB1';
run;
proc print data=name_job1;
title '5) Have name, but no amount';
title2 'Only filter JOB1';
run;
proc print data=amount_for_job1;
title '6) Have amount, but not in name file';
title2 'Only filter JOB1';
run;
proc print data=match_not_job1;
title '7) Match on Company and Job';
title2 'NOT JOB1 Matches';
run;
proc print data=name_not_job1;
title '8) Have name, but no amount';
title2 'Name is NOT JOB1 have no match in amount file';
run;
proc print data=amount_not_job1;
title '9) Have amount, but not in name file';
title2 'Amount is NOT JOB1, have no match in name file';
run;
And the output has been filtered even further so that all the JOB1 rows -- matches and non-matches are in separate files so you can deal with them. Here's the output from all of the above PROC PRINTs, slightly rearranged:
There might be other ways you want to create the output datasets. You might want fewer datasets, you might want to treat the matches and nonmatches differently for the JOB1 rows. At this point, it is just a matter of changing the program logic.
Hope this helps,
cynthia
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
-
2596 views
-
6 likes
-
-
