proc sql joining variables with different subquery from

[michtka]

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,

V.

[michtka]

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

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);

          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;

[PGStats]

What result do you want? - PG

[michtka]

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

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);

          *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;

[PGStats]

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;

PG

[michtka]

any alternative way? thanks.