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, ...).
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.