Hi Everyone!
I am trying to merge two datasets and need some help tweaking my code. I have 10 datasets divided into two groups: Group A = years 2010-2015 and Group B= 2009-2014.
I need to merge 2010 with 2009, 2011 with 2010, etc. I used the following code below which does what I want but also produces extra merges in combinations I don't want (for example, 2010 with 2011, 2011 with 2015, etc). Here's my code:
%let Age=Age3CE;*** Age1CE Age2CE Age3CE Age4CE****;
%let plan=MMC; ****FFS or MMC****;
%macro mergeagesce;
%Local I J;
%DO I=2010 %To 2015;
%DO J=2009 %To 2014;
DATA ONEs TWOs inBOTH NOmatch1 NOmatch2 NOmatch;
MERGE PCNs_%SYSFUNC(PUTN(&I,Z4.))_CAP_&Age._&plan (IN=In1)pqo.PCNs_%SYSFUNC(PUTN(&J,Z4.))_CE (IN=In2);
BY client_nbr;
IF In1=1 then output ONEs;
IF In2=1 then output TWOs;
IF (In1=1 and In2=1) then output inBOTH;
IF (In1=0 and In2=1) then output NOmatch1;
IF (In1=1 and In2=0) then output NOmatch2;
IF (In1+In2)=1 then output NOmatch;
RUN;
%end;
%end;
%mend mergeagesce;
%mergeagesce;
Does anyone know what I can do to only get the merges I want and not the extra merges the code produces? Since I'm essentially just merging one dataset with the previous year dataset, is there a macro I can use that just tells SAS to merge it with the previous year dataset?
I am using SAS version 9.4 for your information.
I really like your idea! However, I am running into a problem with the name of the file. What i did was:
%let Age=Age3CE;*** Age1CE Age2CE Age3CE Age4CE****;
%let plan=MMC; ****FFS or MMC****;
%let minus= %eval(&I-1);
%macro mergeagesce;
%Local I;
%DO I=2010 %To 2015;
%Local J;
%DO J=2009 %To 2014;
DATA ONEs TWOs inBOTH NOmatch1 NOmatch2 NOmatch;
MERGE PCNs_%SYSFUNC(PUTN(&I,Z4.))_CAP_&Age._&plan (IN=In1)pqo.PCNs_&minus._CE (IN=In2);
BY client_nbr;
IF In1=1 then output ONEs;
IF In2=1 then output TWOs;
IF (In1=1 and In2=1) then output inBOTH;
IF (In1=0 and In2=1) then output NOmatch1;
IF (In1=1 and In2=0) then output NOmatch2;
IF (In1+In2)=1 then output NOmatch;
RUN;
%end;
%end;
%mend mergeagesce;
%mergeagesce;
However, when I do this, SAS cannot find the file because the file is not named "PCN_MINUS_CE". Instead, it is named "PCN_2009_CE" and so on (2009-2014). That's why I used the %sysfunc.
To FreelanceReinhard:
I just copied and pasted without seeing I kept the %local J. I did get rid of that, so what I did was:
%macro mergeagesce;
%Local I;
%DO I=2010 %To 2015;
DATA ONEs TWOs inBOTH NOmatch1 NOmatch2 NOmatch;
MERGE PCNs_%SYSFUNC(PUTN(&I,Z4.))_CAP_&Age._&plan (IN=In1)pqo.PCNs_&minus._CE (IN=In2);
BY client_nbr;
IF In1=1 then output ONEs;
IF In2=1 then output TWOs;
IF (In1=1 and In2=1) then output inBOTH;
IF (In1=0 and In2=1) then output NOmatch1;
IF (In1=1 and In2=0) then output NOmatch2;
IF (In1+In2)=1 then output NOmatch;
RUN;
%end;
%mend mergeagesce;
%mergeagesce;
This is not what I suggested. It seems I should have provided more details.
Everything else should remain unchanged (%LOCAL statement, any references to macro variable J, ...).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.