topic Re: Create multiple macro variables to store totals in SAS Programming

Create multiple macro variables to store totals

mariko5797 — Mon, 08 Aug 2022 13:46:42 GMT

I need these totals in the report headers for each dose (DOSENUM), treatment (TRT), and study day (STUDYDAY). It should be something like this (one table for each dose-treatment combo):

Symptom	Severity	Pre-Dose (N=X)		Post-Dose (N=X)		Day 1 (N=X)		Day 2 (N=X)		Day 3 (N=X)		Day 4 (N=X)		Day 5 (N=X)		Day 6 (N=X)		Day 7 (N=X)		Day 8+ (N=X)		Any Post-Dose*
Symptom	Severity	n	%	n	%	n	%	n	%	n	%	n	%	n	%	n	%	n	%	n	%	n	%

The macro variable should fill in the N=X part of the header using PROC REPORT.

Test Data:

data _dose;
 do dosenum = '01', '02', '03'; output; end;
data _trt;
 do trt = 'A', 'B', 'F'; output; end;
data _stdy;
 do studyday = '00', '00B', '01', '02', '03'; output; end;
run;

proc sql;
 create table _tot0 as
 	select dosenum, trt, studyday
		from _dose, _trt, _stdy;
quit;

data _tot1;
 if _n_ = 0 then set _tot0 nobs= N;
 	do i = 1 to N;
		tot = round(rand("Uniform")*10+1);
		output;
	end;

data _tot;
 merge _tot0 _tot1(keep= tot);
run;

Macro + Resulting Warning

%macro totals(no);
	proc sql noprint;
	 select tot into :D&no.A01-:D&no.A11 from _tot where dosenum = "0&no." and trt = 'A';
	 select tot into :D&no.B01-:D&no.B11 from _tot where dosenum = "0&no." and trt = 'B';
	 /*select tot into :D&no.C01-:D&no.C11 from _tot where dosenum = "0&no." and trt = 'C';*/
	 /*select tot into :D&no.D01-:D&no.D11 from _tot where dosenum = "0&no." and trt = 'D';*/
	 /*select tot into :D&no.E01-:D&no.E11 from _tot where dosenum = "0&no." and trt = 'E';*/
	 select tot into :D&no.F01-:D&no.F11 from _tot where dosenum = "0&no." and trt = 'F';
	quit;

%mend;
%totals(1);

%put &=D1A01 &=D1A02 &=D1A03 &=D1A04 &=D1A05 &=D1A06 &=D1A07 &=D1A08 &=D1A09 &=D1A10 &=D1A11
	 &=D1B01 &=D1B02 &=D1B03 &=D1B04 &=D1B05 &=D1B06 &=D1B07 &=D1B08 &=D1B09 &=D1B10 &=D1B11
	 /*&=D1C01 &=D1C02 &=D&no.C03 &=D&no.C04 &=D&no.C05 &=D1C06 &=D&no.C07 &=D&no.C08 &=D&no.C09 &=D&no.C10 &=D&no.C11*/
	 /*&=D1D01 &=D1D02 &=D&no.D03 &=D&no.D04 &=D&no.D05 &=D1D06 &=D&no.D07 &=D&no.D08 &=D&no.D09 &=D&no.D10 &=D&no.D11*/
	 /*&=D1E01 &=D1E02 &=D&no.E03 &=D&no.E04 &=D&no.E05 &=D1E06 &=D&no.E07 &=D&no.E08 &=D&no.E09 &=D&no.E10 &=D&no.E11*/
	 &=D1F01 &=D1F02 &=D1F03 &=D1F04 &=D1F05 &=D1F06 &=D1F07 &=D1F08 &=D1F09 &=D1F10 &=D1F11;

WARNING: Apparent symbolic reference D1A01 not resolved.
/*etc.*/

There is PROC SQL output if I remove the noprint option, but I'm not sure what, if anything, it is storing it as.

Is there a way to see which macro variables I made? I tried %put _all_, but didn't see anything that looked remotely like the name I tried to assign, which leads me to believe it was not storing the variable as a macro at all. In this event, where does my code need adjusting?

Re: Create multiple macro variables to store totals

mariko5797 — Mon, 08 Aug 2022 13:48:09 GMT

Reposting the above macro to look nicer.

%macro totals(no);
	proc sql noprint;
	 select tot into :D&no.A01-:D&no.A11 from _tot where dosenum = "0&no." and trt = 'A';
	 select tot into :D&no.B01-:D&no.B11 from _tot where dosenum = "0&no." and trt = 'B';
	 /*select tot into :D&no.C01-:D&no.C11 from _tot where dosenum = "0&no." and trt = 'C';*/
	 /*select tot into :D&no.D01-:D&no.D11 from _tot where dosenum = "0&no." and trt = 'D';*/
	 /*select tot into :D&no.E01-:D&no.E11 from _tot where dosenum = "0&no." and trt = 'E';*/
	 select tot into :D&no.F01-:D&no.F11 from _tot where dosenum = "0&no." and trt = 'F';
	quit;

%mend;
%totals(1);

%put &=D1A01 &=D1A02 &=D1A03 &=D1A04 &=D1A05 &=D1A06 &=D1A07 &=D1A08 &=D1A09 &=D1A10 &=D1A11
	 &=D1B01 &=D1B02 &=D1B03 &=D1B04 &=D1B05 &=D1B06 &=D1B07 &=D1B08 &=D1B09 &=D1B10 &=D1B11
	 /*&=D1C01 &=D1C02 &=D&no.C03 &=D&no.C04 &=D&no.C05 &=D1C06 &=D&no.C07 &=D&no.C08 &=D&no.C09 &=D&no.C10 &=D&no.C11*/
	 /*&=D1D01 &=D1D02 &=D&no.D03 &=D&no.D04 &=D&no.D05 &=D1D06 &=D&no.D07 &=D&no.D08 &=D&no.D09 &=D&no.D10 &=D&no.D11*/
	 /*&=D1E01 &=D1E02 &=D&no.E03 &=D&no.E04 &=D&no.E05 &=D1E06 &=D&no.E07 &=D&no.E08 &=D&no.E09 &=D&no.E10 &=D&no.E11*/
	 &=D1F01 &=D1F02 &=D1F03 &=D1F04 &=D1F05 &=D1F06 &=D1F07 &=D1F08 &=D1F09 &=D1F10 &=D1F11;

Re: Create multiple macro variables to store totals

Tom — Mon, 08 Aug 2022 14:15:59 GMT

Your test data generation is way too complicated, and also is probably not making the values of STUDYDAY you expected because you cannot put the three character string '00B' into a variable you defined to be only 2 bytes long.

data _tot;
  do dosenum = '01', '02', '03'; 
  do trt = 'A', 'B', 'F'; 
  do studyday = '00 ', '00B', '01 ', '02 ', '03 '; 
    tot = round(rand("Uniform")*10+1);
    output; 
  end;
  end;
  end;
run;

Use CALL SYMPUTX() to generate the macro variables.

Here is an example using a macro so I can define the macro variables into an empty symbol table to make it easier to check their values. In real life you don't need the %TEST macro and you probably do not need to force the macro variables into the local symbol table.

%macro test;
data _null_;
  set _tot;
  call symputx(cats('D',dosenum,trt,studyday),tot,'L');
run;
%put _local_;
%mend test;
%test;

Result:

MPRINT(TEST):   data _null_;
MPRINT(TEST):   set _tot;
MPRINT(TEST):   call symputx(cats('D',dosenum,trt,studyday),tot,'L');
MPRINT(TEST):   run;

NOTE: There were 45 observations read from the data set WORK._TOT.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


TEST D01A00 10
TEST D01A00B 9
TEST D01A01 8
TEST D01A02 10
TEST D01A03 9
TEST D01B00 5
TEST D01B00B 9
TEST D01B01 9
TEST D01B02 11
TEST D01B03 1
TEST D01F00 2
TEST D01F00B 9
TEST D01F01 10
TEST D01F02 5
TEST D01F03 3
TEST D02A00 7
TEST D02A00B 5
TEST D02A01 4
TEST D02A02 9
TEST D02A03 4
TEST D02B00 7
TEST D02B00B 6
TEST D02B01 6
TEST D02B02 3
TEST D02B03 8
TEST D02F00 6
TEST D02F00B 10
TEST D02F01 9
TEST D02F02 5
TEST D02F03 9
TEST D03A00 2
TEST D03A00B 3
TEST D03A01 8
TEST D03A02 3
TEST D03A03 8
TEST D03B00 6
TEST D03B00B 2
TEST D03B01 6
TEST D03B02 6
TEST D03B03 5
TEST D03F00 10
TEST D03F00B 10
TEST D03F01 2
TEST D03F02 4
TEST D03F03 6

Re: Create multiple macro variables to store totals

Quentin — Mon, 08 Aug 2022 14:21:28 GMT

This code:

%macro totals(no);
	proc sql noprint;
	 select tot into :D&no.A01-:D&no.A11 from _tot where dosenum = "0&no." and trt = 'A';
	 select tot into :D&no.B01-:D&no.B11 from _tot where dosenum = "0&no." and trt = 'B';
	 /*select tot into :D&no.C01-:D&no.C11 from _tot where dosenum = "0&no." and trt = 'C';*/
	 /*select tot into :D&no.D01-:D&no.D11 from _tot where dosenum = "0&no." and trt = 'D';*/
	 /*select tot into :D&no.E01-:D&no.E11 from _tot where dosenum = "0&no." and trt = 'E';*/
	 select tot into :D&no.F01-:D&no.F11 from _tot where dosenum = "0&no." and trt = 'F';
	quit;

%mend;
%totals(1);

%put &=D1A01 &=D1A02 &=D1A03 &=D1A04 &=D1A05 &=D1A06 &=D1A07 &=D1A08 &=D1A09 &=D1A10 &=D1A11
	 &=D1B01 &=D1B02 &=D1B03 &=D1B04 &=D1B05 &=D1B06 &=D1B07 &=D1B08 &=D1B09 &=D1B10 &=D1B11
	 /*&=D1C01 &=D1C02 &=D&no.C03 &=D&no.C04 &=D&no.C05 &=D1C06 &=D&no.C07 &=D&no.C08 &=D&no.C09 &=D&no.C10 &=D&no.C11*/
	 /*&=D1D01 &=D1D02 &=D&no.D03 &=D&no.D04 &=D&no.D05 &=D1D06 &=D&no.D07 &=D&no.D08 &=D&no.D09 &=D&no.D10 &=D&no.D11*/
	 /*&=D1E01 &=D1E02 &=D&no.E03 &=D&no.E04 &=D&no.E05 &=D1E06 &=D&no.E07 &=D&no.E08 &=D&no.E09 &=D&no.E10 &=D&no.E11*/
	 &=D1F01 &=D1F02 &=D1F03 &=D1F04 &=D1F05 &=D1F06 &=D1F07 &=D1F08 &=D1F09 &=D1F10 &=D1F11;

Is likely making local macro variables, which only exist inside the macro %totals. If you move the %PUT statement into your macro, does it work like you want?