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

Hello,

 

I used PROC MEANS to sum flags by three variables (ORSID RECORDID LOG).  I now want to create new variables that retain the sums for each flag, then set each flag back to 0 or 1.  I thought it would make my program more efficient if I wrote a macro program, then used an array to run it for each flag variable as below.  The program works...… VERY SLOWLY.  Is there a better way to do this in terms of programming efficiency?

 

proc means data=cohort_claims_1218 sum noprint idmin;
	var claimflag--fp_emergency sti:;
	by orsid recordid log;
	id date;
	output out=flagfreqs sum=;
run;

%macro freqs(var);
	if &var ge 1 then &var._freq = &var;
%mend;
data test;
	set flagfreqs;
	by orsid recordid log;
	array FREQS claimflag--STI_Total;
	do i = 1 to dim(FREQS);
		%freqs(i);
		if FREQS{i} ge 1 then FREQS{i} = 1;
	end;
run;
1 ACCEPTED SOLUTION
7 REPLIES 7
PaigeMiller
Diamond | Level 26

I don't know why a macro runs slowly here, but a macro simply is not needed and is an extra complication that provides no benefit. Oh, I should add that macros are not inherently more efficient than non-macro code, so your reason for going to macro code doesn't hold water.

 

Now, I don't really know what you intended macro %FREQS to accomplish, but you need to go back to the beginning and create SAS code that works for one or two situations without macros.

 

I see nothing that requires macros here anyway, arrays ought to work properly, stick to arrays here.

--
Paige Miller
Reeza
Super User

The %freqs macro seems designed to take a variable name, but instead takes an index, i?

 

if 1 ge 1 then 1_freq = 1;

ie if i=1 this resolves to the following

 

Is that what you intended?

 

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Fully worked examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

Kurt_Bremser
Super User

Your macro resolves to this code, once(!):

if i ge 1 then i_freq = i;

which is then executed dim(freqs) times, so variable i_freq will always end up holding the value of dim(freqs) (the final value of i during the loop).

I seriously doubt that this is what you want.

LEINAARE
Obsidian | Level 7

I see.  Thank you for clarifying that.  I am trying to devise a way to prevent redundant lines of code.  I could code as below, but it would account for many lines.

data test;
	set flagfreqs;
	by orsid recordid log;
	if Var1 ge 1 then Var1_freq = Var1;
	if Var2 ge 1 then Var2_freq = Var2;
	if Var3 ge 1 then Var3_freq = Var3;
	if Var4 ge 1 then Var4_freq = Var4;
	if Var5 ge 1 then Var5_freq = Var5;
	if Var6 ge 1 then Var6_freq = Var6;
	if Var7 ge 1 then Var7_freq = Var7;
	if Var8 ge 1 then Var8_freq = Var8;
	if Var9 ge 1 then Var9_freq = Var9;
	if Var10 ge 1 then Var10_freq = Var10;
	if Var11 ge 1 then Var11_freq = Var11;
	if Var12 ge 1 then Var12_freq = Var12;
	if Var13 ge 1 then Var13_freq = Var13;
	if Var14 ge 1 then Var14_freq = Var14;
	if Var15 ge 1 then Var15_freq = Var15;
	if Var16 ge 1 then Var16_freq = Var16;
	if Var17 ge 1 then Var17_freq = Var17;
	if Var18 ge 1 then Var18_freq = Var18;
	if Var19 ge 1 then Var19_freq = Var19;
	if Var20 ge 1 then Var20_freq = Var20;
	if Var21 ge 1 then Var21_freq = Var21;
	if Var22 ge 1 then Var22_freq = Var22;
	if Var23 ge 1 then Var23_freq = Var23;
	if Var24 ge 1 then Var24_freq = Var24;
	if Var25 ge 1 then Var25_freq = Var25;
	if Var26 ge 1 then Var26_freq = Var26;
	if Var27 ge 1 then Var27_freq = Var27;
	if Var28 ge 1 then Var28_freq = Var28;
	if Var29 ge 1 then Var29_freq = Var29;
	if Var30 ge 1 then Var30_freq = Var30;
	/*There are more than 30 variables, but this gives the gist*/
        array ONES Var1--Var30;
	do over ones;
		if ones GE 1 then ones = 1;
		else ones = 0;
	end;
run;
LEINAARE
Obsidian | Level 7

Hi @Kurt_Bremser,

 

Thank you for the advise.  Below is the program I wrote to experiment.  Is this in line with what you were suggesting?

 

proc means data=cohort_claims_1218 sum noprint idmin;
	var claimflag--fp_emergency sti:;
	by orsid recordid log;
	id date;
	output out=flagfreqs sum=;
run;	*14,624,576;

proc transpose data=flagfreqs (obs=100 drop=_: date)
	out=middle;
	by orsid recordid log;
run;
data middle;
	set middle;
	if COL1 ge 1 then COL2 = 1;
		else COL2 = 0;
run;
proc transpose data=middle
	out=final (drop=_name_)
	suffix=_freq;
	id _name_;
	by orsid recordid log;
	var COL2;
run;
data want;
	merge flagfreqs (obs=100)
		final;
	by orsid recordid log;
run;
Kurt_Bremser
Super User

Yes, this is exactly what I had in mind. Transposing to long lets you use the "natural loop" of the data step iteration without having to resort to array coding.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1032 views
  • 4 likes
  • 4 in conversation