I want to be able to reference different macro variables to add totals to a header like so: Group 1 (N=&N11.), Group 2 (N=&N12.) as so on...
Is there a way to do this all in a single SQL or DATA STEP?
data have;
input id $ armn agen @@;
cards;
A 0 1
B 0 2
C 0 3
D 1 4
E 1 1
F 0 3
G 1 4
H 1 5
;
run;
Attempt with DATA STEP
data _null_;
if 0 then set have nobs= N;
call symputx(cats('N', AGEN, ARMN), N, 'L');
run;
ERROR: Symbolic variable name N-- must contain only letters, digits, and underscores.
I know you can do something like below with PROC SQL, but I'd rather have the variable name associated with ARMN and AGEN.
proc sql;
select count(distinct ID) into :n1-:n8 from have group by ARMN, AGEN;
quit;
In your DATA step, what is the name(s) of the macro variable(s) you want to create?
This message is telling you that the values of ARMN and AGEN contain characters that are not legal for naming a macro variable. It might be as simple as embedded blanks within ARMN and GEN, which the compress function can handle. CATS won't remove embedded blanks.
Why do you have:
if 0 then set
?
That means you re not actually reading in the data from work.have. If you remove that, it works fine:
data have;
input id $ armn agen @@;
cards;
A 0 1
B 0 2
C 0 3
D 1 4
E 1 1
F 0 3
G 1 4
H 1 5
;
run;
data _null_;
/*if 0 then */ set have nobs= N;
call symputx(cats('N', AGEN, ARMN), N, 'L');
run;
%put _user_ ;
Note that "works" means runs without errors. But it will give the same value to every macro var. Which is probably not what you want. If you want to count distinct ID's within groups, you could use by-group processing, something like (untested):
data _null_;
set have ;
by armn agen id;
if first.agen then count=0;
if first.id then count++1;
if last.agen then call symputx(cats('N', AGEN, ARMN), count, 'L');
run;
Storing data in macro variable often leads to unnecessary complexity in the following steps. You may want to explain the next steps, so that we could suggest a better approach.
I want to use them in PROC REPORT, so I can have counts in the headers.
data WANT;
input VAR ARM1AGE1 ARM1AGE2 ARM2AGE1 ARM2AGE2 @@;
cards;
Var1 7 4
Var2 3 9
Var3 8 12
;
run;
proc report data= WANT splitchar= '*';
columns VAR ("Group 1" ARM1AGE1 ARM1AGE2) ("Group 2" ARM2AGE1 ARM2AGE2);
define VAR / display "Variable";
define ARM1AGE1 / display "<18yo*(N=&N11.)";
define ARM1AGE2/ display ">=18yo*(N=&N12.)";
define ARM2AGE1 / display "<18yo*(N=&N21.)";
define ARM2AGE2 / display ">=18yo*(N=&N22.)";
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.