BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wenzli25
Calcite | Level 5

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
		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

4 REPLIES 4
Reeza
Super User
		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)
wenzli25
Calcite | Level 5

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.

Reeza
Super User

@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. 

Astounding
PROC Star

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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