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

Create multiple macro variables to store counts

mariko5797 — Thu, 12 Jan 2023 18:21:29 GMT

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;

Re: Create multiple macro variables to store counts

Astounding — Thu, 12 Jan 2023 18:31:38 GMT

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.

Re: Create multiple macro variables to store counts

Quentin — Thu, 12 Jan 2023 19:01:19 GMT

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;

Re: Create multiple macro variables to store counts

mariko5797 — Thu, 12 Jan 2023 22:47:04 GMT

I would want the variable to be &N[ARMN][AGEN]. For example, the count of
subjects with ARMN=1 and AGEN=3 to be stored as &N13.

Re: Create multiple macro variables to store counts

andreas_lds — Fri, 13 Jan 2023 06:30:51 GMT

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.

Re: Create multiple macro variables to store counts

mariko5797 — Fri, 20 Jan 2023 22:00:29 GMT

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;