DATA Step, Macro, Functions and more

Assign and Retain Values to Variable inside a macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Assign and Retain Values to Variable inside a macro

[ Edited ]

 

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. ^_^


Accepted Solutions
Solution
‎01-26-2018 04:56 AM
Super User
Posts: 23,659

Re: Assign and Retain Values to Variable inside a macro

		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)

View solution in original post


All Replies
Solution
‎01-26-2018 04:56 AM
Super User
Posts: 23,659

Re: Assign and Retain Values to Variable inside a macro

		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)
Occasional Contributor
Posts: 15

Re: Assign and Retain Values to Variable inside a macro

[ Edited ]

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.

Super User
Posts: 23,659

Re: Assign and Retain Values to Variable inside a macro


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. 

Super User
Posts: 6,751

Re: Assign and Retain Values to Variable inside a macro

[ Edited ]

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 189 views
  • 0 likes
  • 3 in conversation