Help using Base SAS procedures

summary proc sql (add a second subquery with different select distinct)

Accepted Solution Solved
Reply
Super Contributor
Posts: 301
Accepted Solution

summary proc sql (add a second subquery with different select distinct)

Hi eaveryone, I

've got the next dataset have, my problem is that I want to make a second query to add 2 new variables pla_total and drug_total to my first query (5 variables).

But as difference for the first query (select distinct subno,trt,time) , this second query need to come from different select ( select distinct subno, trt) to summary

the right number of subjects 3, and 2, rather than 6 and 4, but I dont know how to do it.

                   

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

          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,
          *second query, where need to be created from different select, not how it is at the moment;

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

Cheers,

V.


Accepted Solutions
Solution
‎06-21-2012 01:37 PM
Frequent Contributor
Posts: 95

Re: summary proc sql (add a second subquery with different select distinct)

You already have first part as a subquery. Do the same type of sub querying for the additional two variables and merge the results.

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,

*

from (

select

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,074

Re: summary proc sql (add a second subquery with different select distinct)

In this simple case you could just add the two numbers you just calculated.

data have;

  length subno weight height 8 sex race $20 age 8 trt time $20;

  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

   ,calculated drug_past + calculated drug_present as drug_total

   ,calculated pla_past + calculated pla_present as pla_total

  from (select distinct subno,trt,time from have)

;

quit;

data _null_;

set ;

put (_all_) (=/);

run;

Results:

col0=n subjects

drug_past=3

drug_present=3

pla_past=2

pla_present=2

drug_total=6

pla_total=4

Super Contributor
Posts: 301

Re: summary proc sql (add a second subquery with different select distinct)

Hi Tom, this is exactly like I dont want, I want just subject taking drug=3, and subject taking placebo=2, for this reason I have to play with

select distinct subno, trt, rather than subno, trt, time Smiley Happy

Super User
Super User
Posts: 7,074

Re: summary proc sql (add a second subquery with different select distinct)

Since you only want one row out you can just join the separate queries.

proc sql;

create table want as

  select

    'n subjects' as col0

   ,sum(trt='DRUG' and time='PAST') as drug_past

   ,sum(trt='DRUG' and time='PRESENT') as drug_present

   ,sum(trt='PLACEBO' and time='PAST') as pla_past

   ,sum(trt='PLACEBO' and time='PRESENT') as pla_present

   ,max(drug_total) as drug_total

   ,max(pla_total) as pla_total

  from (select distinct subno,upcase(trt) as trt,upcase(time) as time from have)

     , (select count(distinct subno) as drug_total from have where upcase(trt)='DRUG')

     , (select count(distinct subno) as pla_total from have where upcase(trt)='PLACEBO')

;

quit;

col0=n subjects

drug_past=3

drug_present=3

pla_past=2

pla_present=2

drug_total=3

pla_total=2


You can also just skip SQL and use SAS procedures.  Then you do not need to hard code values of TRT or TIME.


proc sql noprint ;

  create view usubj as select distinct subno,trt,time from have;

quit;

proc summary data=usubj ;

class trt time  ;

types trt trt*time;

output out=summary;

run;

proc transpose data=summary out=want1 (drop=_Smiley Happy delimiter=_ ;

  where _type_=3;

  id trt time;

  var _freq_;

run;

proc transpose data=summary out=want2 (drop=_Smiley Happy suffix=_total ;

  where _type_=2;

  id trt;

  var _freq_;

run;

data want;

  merge want1 want2 ;

run;

Super Contributor
Posts: 301

Re: summary proc sql (add a second subquery with different select distinct)

Thank you  Tom, I like you sql approach, it works well.

Just only, I am workin out the power of proc sql.

Respected Advisor
Posts: 4,930

Re: summary proc sql (add a second subquery with different select distinct)

How about :

   proc sql;
    create table uhave as
    select "n subjects" as col0, lowcase(trt) as utrt, lowcase(time) as utime, count(distinct subno) as n from have group by utrt, utime
    union all
    select "n subjects" as col0, lowcase(trt) as utrt, "total" as utime, count(distinct subno) from have group by utrt;

    proc transpose data=uhave out=want(drop=_Smiley Happy delim=_;
    by col0;
    var n;
    id utrt utime;
    run;

PG

PG
Solution
‎06-21-2012 01:37 PM
Frequent Contributor
Posts: 95

Re: summary proc sql (add a second subquery with different select distinct)

You already have first part as a subquery. Do the same type of sub querying for the additional two variables and merge the results.

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,

*

from (

select

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;

Super Contributor
Posts: 301

Re: summary proc sql (add a second subquery with different select distinct)

Alpay, thnak you very much, this is like I was looking for...the way to select variables from different select distinct, It works well,

but please, can you explain me the function of the asterisk after col0?

I will appreciate this, thanks.

Frequent Contributor
Posts: 95

Re: summary proc sql (add a second subquery with different select distinct)

Asterisk will select all columns in underlying tables.

In this case, two sub queries in above sql script act as tables. One sub query returns 4 variables, and the other one returns 2 variables.

For more explanation, please refer to PROC SQL documentation in help file.

Super Contributor
Posts: 301

Re: summary proc sql (add a second subquery with different select distinct)

Thank you very much.

V.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 482 views
  • 6 likes
  • 4 in conversation