topic proc sql macrovariables treatment in SAS Procedures

proc sql macrovariables treatment

michtka — Sat, 02 Jun 2012 14:44:12 GMT

Hi, I would like to summary the variable treatment: drug, placebo of the next dataset have:

subjid treatment

1 drug

2 placebo

3 drug

4 drug

5 placebo

using this syntax:

proc sql;

select treatment, count (distinct subjid) as count

from have;

quit;

I will get :

placebo 2

drug 3

But my idea is using proc sql , i will keep these values 2, and 3 in diffrent macrovariables using proc sql with the option (into),

but i need the syntax to do it.

Any help:

Thanks in advance.

variabels

Re: proc sql macrovariables treatment

michtka — Sat, 02 Jun 2012 14:45:42 GMT

sorry...i missed the group

proc sql;

select treatment, count (distinct subjid) as count

from have

group by treatment;

quit;

Re: proc sql macrovariables treatment

Tom — Sat, 02 Jun 2012 15:21:44 GMT

You can get them assigned to a series of macro variables this way, but make sure you can recreate the order to match them up later with the proper treatments.

proc sql noprint;

select count(distinct subjid) into :ntrt1 - :ntrt2

group by treatment

order by treatment

;

There are two issues with this.

First you will get a warning about grouping by a variable that is not included in the selection list.

You can either also pull out the treatments into another macro variable array (if it is useful for you), or if you do not need it just shove into a dummy macro variable to suppress the warning.

select treatment,count(distinct subjid)

into dummy,:ntrt1 - :ntrt2

group by treatment

order by treatment

;

Notice that the list of macro variables is after the INTO keyword and separated by commas just like the list of values that starts the select statement.

The other problem is how many macro variables will you need? If you are creating generic code then that might not be known in advance. You can give it an extreme value for the upperrange in the INTO clause and it will only create the macro variables that it needs. You can then reference the automatic macro variable SQLOBS to find how many groups there were.

Combining these we could create two macro variable arrays (TRTxx and NTRTxx) and the count (NTRTGRP) using this statement.

select

treatment

,count(distinct subjid)

into :trt1 - :trt200

, :ntrt1 - :ntrt200

group by treatment

order by treatment

;

%let ntrtgrp = &sqlobs ;