Create multiple macro variables to store totals

[mariko5797]

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*
		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?

[mariko5797]

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;

[Tom · Accepted solution]

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

[Quentin]

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?