Help using Base SAS procedures

proc sql joining variables with different subquery from

Reply
Super Contributor
Posts: 301

proc sql joining variables with different subquery from

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.

Super Contributor
Posts: 301

Re: proc sql joining variables with different subquery from

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;

Respected Advisor
Posts: 4,920

Re: proc sql joining variables with different subquery from

What result do you want? - PG

PG
Super Contributor
Posts: 301

Re: proc sql joining variables with different subquery from

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;

Respected Advisor
Posts: 4,920

Re: proc sql joining variables with different subquery from

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=_Smiley Happy delim=_;
  var count;
  id trt time;
  run;

PG

PG
Super Contributor
Posts: 301

Re: proc sql joining variables with different subquery from

any alternative way? thanks.

Ask a Question
Discussion stats
  • 5 replies
  • 230 views
  • 0 likes
  • 2 in conversation