BookmarkSubscribeRSS Feed
michtka
Fluorite | Level 6

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.

5 REPLIES 5
michtka
Fluorite | Level 6

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
Opal | Level 21

What result do you want? - PG

PG
michtka
Fluorite | Level 6

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
Opal | Level 21

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

PG
michtka
Fluorite | Level 6

any alternative way? thanks.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 779 views
  • 0 likes
  • 2 in conversation