topic Re: Using an array efficantly with a macro in New SAS User

Using an array efficantly with a macro

LEINAARE — Thu, 12 Dec 2019 22:04:59 GMT

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;

Re: Using an array efficantly with a macro

PaigeMiller — Thu, 12 Dec 2019 22:48:58 GMT

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.

Re: Using an array efficantly with a macro

Reeza — Thu, 12 Dec 2019 23:17:06 GMT

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-Appendix/ta-p/291716

Re: Using an array efficantly with a macro

Kurt_Bremser — Fri, 13 Dec 2019 07:41:15 GMT

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.

Re: Using an array efficantly with a macro

LEINAARE — Fri, 13 Dec 2019 12:22:51 GMT

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;

Re: Using an array efficantly with a macro

Kurt_Bremser — Fri, 13 Dec 2019 12:28:03 GMT

Transpose the dataset to a long format, and the code will be dead simple.

Re: Using an array efficantly with a macro

LEINAARE — Fri, 13 Dec 2019 13:22:30 GMT

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;

Re: Using an array efficantly with a macro

Kurt_Bremser — Fri, 13 Dec 2019 14:42:28 GMT

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.