proc sql macrovariables treatment

[michtka]

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.

V

variabels

[michtka]

sorry...i missed the group

proc sql;

select treatment, count (distinct subjid) as count

from have

group by treatment;

quit;

[Tom · Accepted solution]

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 ;