Proc merge macro overwrites variables

Abishekaa — Tue, 28 Nov 2023 06:00:55 GMT

When I run this macro, I get multiple overwritten notes for variables visit and parm in the SAS log. I am confused since these variables are dropped in the data step. How can I change the code so that visit and parm variables are not overwritten anymore?

data test;
    merge test2(in=inA)
		
		  %macro merge(parm=,var=,and=);
             test3(keep=PtID Visit parm value0 
	                  where=(parm="&parm" and visit="Baseline" &and)
			          rename=(value0=&var._0 ))
	         test3(keep=PtID Visit parm value valueChg valueChgPct 
	                  where=(parm="&parm" and visit="48 Month Follow-Up" &and)
			          rename=(value=&var._48 valueChg=&var.Chg valueChgPct=&var.ChgPct))
          %mend;

		  %merge(parm=parm1,var=var1)
		  %merge(parm=parm2,var=var2)
		  %merge(parm=parm3,var=var3)
		  %merge(parm=parm4,var=var4)
		  %merge(parm=parm5,var=var5);
		  
	by Pt;
	if inA;
	drop visit parm;
run;

Multiple Log notes like this:

INFO: The variable visit on data set WORK.TEST3 will be overwritten by data set WORK.TEST3.

INFO: The variable parm on data set WORK.TEST3 will be overwritten by data set WORK.TEST3.

Re: Proc merge macro overwrites variables

yabwon — Tue, 28 Nov 2023 07:29:20 GMT

1) variables are dropped but at the "very end" of the process (just for clarification, moving the "drop" statement at the beginning of the data step code will not change that).

2) read this article by Bob Virgille: How MERGE Really Works so you will understand what is happening under the hood.

3) good programming practice is to keep source code of your macro outside the data step, after all macros suppose to be (among others) "wrappers for reusable code", right?

4) since this works:

data test;
  set sashelp.class(
    rename=(age=abcdef)
    where=(abcdef=12)
  )
  ;
run;

then this modification should too (you didn't provided any test data, so I can test it myself)

%macro merge(parm=,var=,and=);
  %local f s;
  %let f=______A&sysindex.;
  %let s=______B&sysindex.;
  test3(keep=PtID Visit parm value0
  rename=(Visit=V&f. parm=P&f.) 
  where=(P&f.="&parm" and V&f.="Baseline" &and)
  rename=(value0=&var._0 ))
  test3(keep=PtID Visit parm value valueChg valueChgPct
  rename=(Visit=V&s. parm=P&s.)  
  where=(P&s.="&parm" and V&s.="48 Month Follow-Up" &and)
  rename=(value=&var._48 valueChg=&var.Chg valueChgPct=&var.ChgPct))
%mend;

and at the end of the data step:

drop V______: P______: ;

to drop unnecessary variables.

Bart

topic Proc merge macro overwrites variables in SAS Programming

Proc merge macro overwrites variables

Re: Proc merge macro overwrites variables