topic Re: Assign and Retain Values to Variable inside a macro in SAS Programming

Assign and Retain Values to Variable inside a macro

wenzli25 — Tue, 23 Jan 2018 15:41:02 GMT

Hi all,

I am trying to create a macro to dynamically compute cumulative returns across variables in my dataset.

And my problem is: the macro below is overwriting the results obtained for the prior variable instead of creating separate columns in each iteration.

Here is my sample dataset:

data work.AAA; 
input id $ GRP $ month txn ; 
datalines;
ID1 A 1 100
ID1 A 1 200
ID1 A 2 300
ID2 A 1 200
ID2 B 2 300
ID2 A 3 400;

My SAS code is as follows:

%macro multmn(startmonth,stopmonth);
	%do mvalue=&startmonth %to &stopmonth;
		Data WORK.CCC;
		SET work.AAA;
		BY ID;
		IF FIRST.ID = 1 THEN DO;
		Trans_CNT_&mvalue=0;
		END;
		Retain Trans_CNT_&mvalue;
		IF (GRP='A' and MONTH=&mvalue) then Trans_CNT_&mvalue = SUM(Trans_CNT_&mvalue+1) ;
		run;
	%end;
%mend multmn;
%multmn(1,3)

In fact, I want to convert the code below into macro method.

Data WORK.BBB;
SET work.AAA;
BY ID;
IF FIRST.ID = 1 THEN DO;
Trans_CNT_1=0;Trans_CNT_2=0;Trans_CNT_3=0;
END;
Retain Trans_CNT_1 Trans_CNT_2 Trans_CNT_3;
IF (GRP='A' and MONTH=1) THEN Trans_CNT_1 = SUM(Trans_CNT_1+1);
IF (GRP='A' and MONTH=2) THEN Trans_CNT_2 = SUM(Trans_CNT_2+1);
IF (GRP='A' and MONTH=3) THEN Trans_CNT_3 = SUM(Trans_CNT_3+1);
run;

If you have any ideas, please advise me. Much thanks. ^_^

Re: Assign and Retain Values to Variable inside a macro

Reeza — Tue, 23 Jan 2018 15:43:16 GMT

		Data WORK.CCC;
		SET work.AA

The lines above are the issue. Since you're always starting from data set AA, you lose the previous run of data. You need to start from the same data set each time to add it on. But why not move your loop into the macro instead?

If you have SAS/ETS then PROC EXPAND will also do this a lot easier.

The following modification should make your program work.

%macro multmn(startmonth,stopmonth);

data ccc;
set aaa;
run;


	%do mvalue=&startmonth %to &stopmonth;
		Data WORK.CCC;
		SET work.ccc;
		BY ID;
		IF FIRST.ID = 1 THEN DO;
		Trans_CNT_&mvalue=0;
		END;
		Retain Trans_CNT_&mvalue;
		IF (GRP1='A' and MONTH=&mvalue) then Trans_CNT_&mvalue = SUM(Trans_CNT_&mvalue+1) ;
		run;
	%end;
%mend multmn;
%multmn(1,3)

Re: Assign and Retain Values to Variable inside a macro

Astounding — Tue, 23 Jan 2018 16:48:12 GMT

What you are describing is part macros, part arrays. For example:

Data WORK.CCC;
SET work.AAA;
BY ID;

array counts {&startmonth:&stopmonth} Trans_CNT_&startmonth - Trans_CNT_&stopmonth;
IF FIRST.ID = 1 THEN DO k=&startmonth to &stopmonth;
counts{k}=0;
END;

if GRP='A' and (&startmonth <= month <= &stopmonth) then counts{month} + 1;

drop k;

run;

This way, you only need to process work.AAA once. If you process the data separately for each month, that will multiply the time it takes your program to run.

Re: Assign and Retain Values to Variable inside a macro

wenzli25 — Tue, 23 Jan 2018 16:04:29 GMT

Thank you for your quick reply. ^_^

I have two problems:

1. I don't understand the meaning of the sentence below:

"But why not move your loop into the macro instead?"

2. So I just take the same dataset to another iteration, then the problem solved, right?

		Data work.ccc;
		SET work.ccc;

Much Thanks.

Re: Assign and Retain Values to Variable inside a macro

Reeza — Fri, 26 Jan 2018 16:09:01 GMT

@wenzli25 wrote:

Thank you for your quick reply. ^_^

I have two problems:

1. I don't understand the meaning of the sentence below:

"But why not move your loop into the macro instead?"

2. So I just take the same dataset to another iteration, then the problem solved, right?
		Data work.ccc;
		SET work.ccc;
Much Thanks.

1. You're looping the data step, when you really should be looping within the data step. Otherwise you're recreating that data set multiple times with additions/changes that doesn't make sense. You should likely be using an array instead of a macro here.

2. Only way to know, is to test it.