DATA Step, Macro, Functions and more

Advanced Merging multiple datasets

Reply
Contributor
Posts: 45

Advanced Merging multiple datasets

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. 

Trusted Advisor
Posts: 1,117

Re: Advanced Merging multiple datasets

Posted in reply to TXSASneophyte

Hi @TXSASneophyte,

 

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

%let J = %eval(&I-1);
Contributor
Posts: 45

Re: Advanced Merging multiple datasets

Posted in reply to FreelanceReinhard

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. 

 

 

Contributor
Posts: 45

Re: Advanced Merging multiple datasets

Posted in reply to TXSASneophyte

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;

Trusted Advisor
Posts: 1,117

Re: Advanced Merging multiple datasets

Posted in reply to TXSASneophyte

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

 

  1. "omit the inner %DO loop" meant: Delete the %DO J=... and the corresponding %END statement. (You've done this. Good.)
  2. 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, ...).

Ask a Question
Discussion stats
  • 4 replies
  • 269 views
  • 1 like
  • 2 in conversation