topic Re: How to create a merge macro? in SAS Programming

How to create a merge macro?

ybz12003 — Thu, 17 Feb 2022 14:59:55 GMT

Hello,

I would like to create a macro to merge the sample data sets as I show below. There are a total of ten of them, I don't list all the them.

data ARIunderly_1617;
	set hou1617_ kc1617_ pit1617_ ;
run;

My macro code is shown below. I know the code is not right, any idea how to fix it? Thanks.

%let X1=VAN;
%let X2=ROC;
%let X3=SEA; 

%macro aaa;

%do i  = 1 %to 3;
data underly_1617;
	set &&X&i..1617_;
run;

%end; 
%mend;
%aaa;

Re: How to create a merge macro?

Kurt_Bremser — Thu, 17 Feb 2022 15:15:13 GMT

Your non-macro code shows one DATA step with one SET statement with three datasets, not three separate DATA steps with a SET statement each, so you need to make your macro create such a statement.

Instead of

%do i  = 1 %to 3;
data underly_1617;
	set &&X&i..1617_;
run;
%end;

you need

data underly_1617;
set
%do i  = 1 %to 3;
  &&X&i..1617_
%end;
; /* this ends the SET */
run;

Re: How to create a merge macro?

Kurt_Bremser — Thu, 17 Feb 2022 15:16:38 GMT

BTW your subject line is wrong, this is not a MERGE, it's a SET, or also called "stacking".

Re: How to create a merge macro?

ybz12003 — Thu, 17 Feb 2022 16:45:02 GMT

I got an error message. Where did I do wrong?

%macro merge;
data underly_1617;
	set
	%do i  = 1 %to 6;
	  &&X&i..1617_
	%end;
run;	
%mend;
%merge;

MPRINT(MERGE): data underly_1617;
MLOGIC(MERGE): %DO loop beginning; index variable I; start value is 1; stop value is 6; by value is
1.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable X1 resolves to VAN
MLOGIC(MERGE): %DO loop index variable I is now 2; loop will iterate again.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 2
SYMBOLGEN: Macro variable X2 resolves to ROC
MLOGIC(MERGE): %DO loop index variable I is now 3; loop will iterate again.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 3
SYMBOLGEN: Macro variable X3 resolves to SEA
MLOGIC(MERGE): %DO loop index variable I is now 4; loop will iterate again.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 4
SYMBOLGEN: Macro variable X4 resolves to HOU
MLOGIC(MERGE): %DO loop index variable I is now 5; loop will iterate again.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 5
SYMBOLGEN: Macro variable X5 resolves to KC
MLOGIC(MERGE): %DO loop index variable I is now 6; loop will iterate again.
SYMBOLGEN: && resolves to &.
SYMBOLGEN: Macro variable I resolves to 6
SYMBOLGEN: Macro variable X6 resolves to PIT
MLOGIC(MERGE): %DO loop index variable I is now 7; loop will not iterate again.
MPRINT(MERGE): set VAN1617_ ROC1617_ SEA1617_ HOU1617_ KC1617_ PIT1617_ run;
ERROR: File WORK.RUN.DATA does not exist.
MLOGIC(MERGE): Ending execution.

Re: How to create a merge macro?

PaigeMiller — Thu, 17 Feb 2022 17:02:59 GMT

You didn't end the SET statement with a semi-colon (which was earlier in this thread specifically pointed out by @Kurt_Bremser)

%macro merge;
data underly_1617;
    set
        %do i = 1 %to 6;
            &&X&i..1617_
        %end;
    ;    /* This semi-colon ends the SET statement */
run;	
%mend;
%merge

Use of proper indenting in your code (as I have done) helps find these types of problems. Don't write code without proper indenting.

Re: How to create a merge macro?

Kurt_Bremser — Thu, 17 Feb 2022 17:17:04 GMT

Failure to copy/paste.

Re: How to create a merge macro?

ybz12003 — Thu, 17 Feb 2022 17:22:12 GMT

Yes, forgot to add semicolon at the end.

Re: How to create a merge macro?

Tom — Thu, 17 Feb 2022 18:26:45 GMT

It is much clearer if you indent the MACRO statements independently from the SAS statements.

Right now you have this inconsistent indentation:

For the macro code you are using 8 spaces of indentation.

For the SAS code you are using 4 in some places and 8 in others.

Instead be consistent. Then when you are trying to see how the logic of the macro works it is easier. And you are trying to follow the flow of the SAS code the macro is generating it is also easier.

%macro merge;
data underly_1617;
    set
    %do i = 1 %to 6;
        &&X&i..1617_
    %end;
    ; 
run;	
%mend;