Date:         Thu, 5 Jun 2008 11:06:27 -0400
Reply-To:     msz03@albany.edu
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Mike Zdeb <msz03@ALBANY.EDU>
Subject:      Re: OT: Chance to Make SAS-L History: Did You Know That...
Content-Type: text/plain;charset=iso-8859-1

hi ... for the "historical record" ... how about at least one non-SQL approach ...

* the data;

data demog;
input treat subjno @@;
datalines;
1 101 1 101 1 102 2 201 4 401 4 402 4 403
;
run;

* don't care about missing treatments;

%symdel trt1 trt2 trt3 trt4;

proc summary data=demog nway;
class treat subjno;
output out=x;
run;

data _null_;
do _n_=1 by 1 until (last.treat);
   set x;
   by treat;
end;
call symputx(cat('trt',treat),_n_);
run;

%put trt1=[&trt1] trt2=[&trt2] trt3=[&trt3] trt4=[&trt4];


WARNING: Apparent symbolic reference TRT3 not resolved.
207  %put trt1=[&trt1] trt2=[&trt2] trt3=[&trt3] trt4=[&trt4];
trt1=[2] trt2=[1] trt3=[&trt3] trt4=[3]


* do care about missing treatments;

%symdel trt1 trt2 trt3 trt4;

proc format;
value treat 1='1' 2='2' 3='3' 4='4';
run;

proc summary data=demog nway completetypes;
class treat  / preloadfmt;
class subjno;
format treat treat.;
output out=x;
run;

data _null_;
do until (last.treat);
   set x;
   by treat;
   count = sum(count, (_freq_ ne 0));
end;
call symputx(cat('trt',treat),count);
run;

%put trt1=[&trt1] trt2=[&trt2] trt3=[&trt3] trt4=[&trt4];


233  %put trt1=[&trt1] trt2=[&trt2] trt3=[&trt3] trt4=[&trt4];
trt1=[2] trt2=[1] trt3=[0] trt4=[3]


--
Mike Zdeb
U@Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> Wow Howard,
>
> That is another way of doing it
>
> thanks
>
>
>> Test data:
>>   data demog;
>>   input treat subjno;
>>   cards;
>>   1 101
>>   1 101
>>   1 102
>>   2 201
>>   4 401
>>   4 402
>>   4 403
>>   ;
>> All of the macro variables can be created in a single query:
>>   %symdel trt1 trt2 trt3 trt4;
>>   proc sql noprint;
>>   select   put(count(distinct
>>             case when treat=1 then subjno else . end),5.-L)
>>          , put(count(distinct
>>             case when treat=2 then subjno else . end),5.-L)
>>          , put(count(distinct
>>             case when treat=3 then subjno else . end),5.-L)
>>          , put(count(distinct
>>             case when treat=4 then subjno else . end),5.-L)
>>    into : trt1, : trt2, : trt3, : trt4
>>    from demog;
>>   quit;
>>   %put trt1=[&trt1] trt2=[&trt2] trt3=[&trt3] trt4=[&trt4];
>> Result: trt1=[2    ] trt2=[1    ] trt3=[0    ] trt4=[3    ]
>> Notice the zero for the absent TREAT=3.
>> If you just want the TREAT levels which exist, it can be done indirectly:
>>   %symdel trt1 trt2 trt3 trt4;
>>   proc sql noprint;
>>   select   cats('"',count(distinct subjno),'"')
>>          , cats(': trt',treat)
>>    into : countlist separated by ',', : treatlist separated by ',' from demog
>>    group by treat;
>>   select &countlist into &treatlist from demog(obs=1);
>>   quit;
>>   %put trt1=[&trt1] trt2=[&trt2] trt3=[&trt3] trt4=[&trt4];
>> After macro variable substitution, the final SELECT statement is:
>>   select "2","1","3" into : trt1,: trt2,: trt4 from demog(obs=1);
>> Result:
>>   WARNING: Apparent symbolic reference TRT3 not resolved.
>>   trt1=[2] trt2=[1] trt3=[&trt3] trt4=[3]
>
>