topic Re: proc sql joining variables with different subquery from in SAS Procedures

proc sql joining variables with different subquery from

michtka — Thu, 21 Jun 2012 14:15:49 GMT

Hi gus, my proc sql doesn't work, to get a final dataset with 7 variables, anyone can help me with this?

data have;

length sex race trt time $20;

length subno weight height age 8;

input subno weight height sex race age trt time;

datalines;

                       1      63.7     141.85    Male      Caucasian     19    Drug     present
                       1      63.7     141.85    Male      Caucasian     19    Drug     past
                       2      72.6     149.22    Female    Black         27    Placebo present
                       2      72.6     149.22    Female    Black         27    Placebo past
                       3      60.7     147.64    Female    Caucasian     26    drug     present
                       3      60.7     147.64    Female    Caucasian     26    drug     past
                       4      64.2     150.93    Male      Caucasian     21    Placebo present
                       4      64.2     150.93    Male      Caucasian     21    Placebo past
                       5      60.6     153.56    Male      Asian         41    drug     present
                       5      60.6     153.56    Male      Asian         41    drug     past
                      run;

          proc sql;
          create table want as
          select 'n subjects' as col0,
          sum(upcase(trt)='DRUG' and upcase(time)='PAST') as drug_past,
          sum(upcase(trt)='DRUG' and upcase(time)='PRESENT') as drug_present,
          sum(upcase(trt)='PLACEBO' and upcase(time)='PAST') as pla_past,
          sum(upcase(trt)='PLACEBO' and upcase(time)='PRESENT') as pla_present
          from (select distinct subno,trt,time from have),

          (select sum(upcase(trt)='DRUG') as drug_total,
          sum(upcase(trt)='PLACEBO') as pla_total
          from (select distinct subno,trt from have));
          quit;

Thnaks,

Re: proc sql joining variables with different subquery from

michtka — Thu, 21 Jun 2012 15:05:21 GMT

this is the only way I can do it. any other option?

          create table want2 as
          select 'n subjects' as col0, sum(upcase(trt)='DRUG') as drug_total,
          sum(upcase(trt)='PLACEBO') as pla_total
          from (select distinct subno,trt from have);
          quit;

          data want3;
          merge want want2;
          by col0;
          run;

Re: proc sql joining variables with different subquery from

PGStats — Thu, 21 Jun 2012 15:07:30 GMT

What result do you want? - PG

Re: proc sql joining variables with different subquery from

michtka — Thu, 21 Jun 2012 15:18:36 GMT

want3, but, I was thinking to obtain want3 in the same query...something like that:

          *I am traing to join this two variables (drug_total and pla_total) for different select to the   previous 5 ones to obtain a table (want3) with 7 variables;
          select 'n subjects' as col0, sum(upcase(trt)='DRUG') as drug_total,
          sum(upcase(trt)='PLACEBO') as pla_total
          from (select distinct subno,trt from have);
          quit;

Re: proc sql joining variables with different subquery from

michtka — Thu, 21 Jun 2012 16:02:42 GMT

any alternative way? thanks.

Re: proc sql joining variables with different subquery from

PGStats — Thu, 21 Jun 2012 16:39:07 GMT

How about other SAS tools :

proc freq data=have noprint;
format trt time $upcase.;
tables trt*time / out=h2Count;
tables trt / out=h1Count;
run;

proc sql;
create table lcount as
select * from h2count
union corr
select *, "TOTAL" as time from h1count;
drop table h1count, h2count;
quit;

proc transpose data=lcount out=want(drop=_:) delim=_;
var count;
id trt time;
run;