topic Advanced Merging multiple datasets in SAS Programming

Advanced Merging multiple datasets

TXSASneophyte — Fri, 13 May 2016 15:29:18 GMT

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;

%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.

Re: Advanced Merging multiple datasets

FreelanceReinh — Fri, 13 May 2016 15:36:55 GMT

Hi @TXSASneophyte,

I think you can omit the inner %DO loop and just define

%let J = %eval(&I-1);

Re: Advanced Merging multiple datasets

TXSASneophyte — Fri, 13 May 2016 16:16:13 GMT

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;

%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.

Re: Advanced Merging multiple datasets

TXSASneophyte — Fri, 13 May 2016 16:17:56 GMT

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;

Re: Advanced Merging multiple datasets

FreelanceReinh — Fri, 13 May 2016 16:27:50 GMT

This is not what I suggested. It seems I should have provided more details.

"omit the inner %DO loop" meant: Delete the %DO J=... and the corresponding %END statement. (You've done this. Good.)
The definition %let J = %eval(&I-1); replaces the %DO J=... statement. It defines J depending on the value of I rather than independently as the %DO statement did.

Everything else should remain unchanged (%LOCAL statement, any references to macro variable J, ...).