Help using Base SAS procedures

created a variable (sum new variable created) in the same proc sql

Reply
Super Contributor
Posts: 301

created a variable (sum new variable created) in the same proc sql

Hi everyone, i got the next dataset:                  

                      data newtot;

                      length sex race trt $20;

                      length subno weight height age 8;

                      input subno    weight    height     sex      race   age    trt;

                      datalines;


                       1      63.7     141.85    Male      Caucasian     19    Drug

                       2      72.6     149.22    Female    Black         27    Placebo

                       3      60.7     147.64    Female    Caucasian     26    Placebo

                       4      64.2     150.93    Male      Caucasian     21    Placebo

                       5      60.6     153.56    Male      Asian         41    Placebo

                       6      66.8     139.30    Male      Caucasian     50    Placebo

                       7      75.5     147.93    Female    Caucasian     24    Drug

                       8      55.8     137.51    Female    Caucasian     21    Placebo

                       9      73.7     146.30    Male      Caucasian     28    Drug

                      10      68.2     133.28    Male      Caucasian     24    Drug

                      11      56.3     148.04    Female    Caucasian     30    Drug

                      12      60.7     141.53    Male      Black         29    Drug

                      13      63.2     147.19    Female    Caucasian     21    Placebo

                      14      74.7     155.35    Male      Black         20    Placebo

                      15      70.8     152.61    Female    Caucasian     33    Drug

                      16      71.4     147.75    Male      Black         37    Drug

                      17      60.1     162.40    Female    Caucasian     28    Placebo

                      18      53.4     152.24    Male      Caucasian     19    Placebo

                      19      65.4     142.22    Male      Caucasian     37    Drug

                      20      52.7     161.64    Female    Black         34    Placebo

                      21      57.9     153.62    Female    Black         33    Drug

                      22      62.2     147.84    Male      Black         29    Drug

                      23      72.9     139.20    Male      Black         21    Placebo

                      24      67.4     143.55    Male      Caucasian     20    Placebo

                      25      69.4     154.10    Female    Caucasian     31    Drug

                      26      72.1     149.09    Male      Caucasian     36    Drug

                      27      64.6     152.56    Female    Black         25    Placebo

                      28      75.1     155.37    Female    Caucasian     19    Placebo

                      29      63.1     153.64    Female    Black         37    Drug

                      30      54.8     149.17    Female    Black         34    Drug

                      31      75.5     149.39    Female    Black         26    Placebo

                      32      72.7     149.27    Male      Caucasian     29    Drug

                      33      68.2     149.57    Male      Caucasian     11    Placebo

                      34      69.0     152.04    Female    Caucasian     20    Drug

                      35      57.9     151.13    Male      Caucasian     31    Placebo

                      36      61.5     138.67    Male      Caucasian     56    Drug

                      37      55.5     159.60    Male      Black         25    Placebo

                      38      71.0     148.42    Male      Oriental      19    Drug

                      39      85.1     152.91    Female    Black         37    Drug

                      40      58.2     148.92    Female    Caucasian     24    Drug

                      ;

                      run;

                     

                    there is any form to create a extra variable total2 in the same proc sql as sum of the new variable total  generated in this proc sql ? Thanks in Advance. V

                     proc sql;

                     create table new as

                     select sex as col0 'Sex', sum( trt='Drug') as trt1, sum (trt='Placebo') as trt2, count(trt) as total,

                     *there are some option to calculated total2 in this proc sql?;

                     from (select distinct subno,trt,sex,race  from newtot)

                     group by sex;

                     quit;

Respected Advisor
Posts: 4,663

Re: created a variable (sum new variable created) in the same proc sql

I guess you can do this (untested):

proc sql; 

                     create table new as

                     select *, sum(total) as total2 from

                     ( select sex as col0 'Sex', sum( trt='Drug') as trt1, sum (trt='Placebo') as trt2, count(trt) as total

                     from (select distinct subno, trt, sex, race  from newtot)

                     group by sex ) ;

                     quit;

PG

PG
Super Contributor
Posts: 301

Re: created a variable (sum new variable created) in the same proc sql

Thank you very much, it works.

i need to click like Correct answer, but it does not appear at the moment in this discussion.

Super User
Super User
Posts: 6,502

Re: created a variable (sum new variable created) in the same proc sql

Just use the () to create a sub query.  The () that you are already using is not needed.


proc sql;

  create table new as

   select *,sum(total) as total2

   from

      (select

          sex as col0 'Sex'

        , sum( trt='Drug') as trt1

        , sum (trt='Placebo') as trt2

        , count(trt) as total

      from newtot

      group by sex

     )

  ;

quit;

Super Contributor
Posts: 301

Re: created a variable (sum new variable created) in the same proc sql

Hi tom, I think you removed the distinct part.

I think, your code is right for this specific example with not duplicate records, but

I would like to consider it to remove possible duplicate records by subno, trt....for example.

Respected Advisor
Posts: 4,663

Re: created a variable (sum new variable created) in the same proc sql

But I don't see why you can't do simplify to:

proc sql; 

create table new as

select *, sum(total) as total2 from

                     ( select sex, sum( trt='Drug') as trt1, sum (trt='Placebo') as trt2, count(trt) as total

                     from newtot group by sex ) ;

quit;

PG

PG
Super User
Posts: 17,963

Re: created a variable (sum new variable created) in the same proc sql

Similar but slightly different answer, works as well by adding in a second subquery.

proc sql;

create table new as

select sex as col0 'Sex', sum( trt='Drug') as trt1, sum (trt='Placebo') as trt2, count(trt) as total, overall_total

/*there are some option to calculated total2 in this proc sql?;*/

from (select distinct subno,trt,sex,race  from newtot) as a

cross join (select count(trt) as overall_total from newtot) as b

group by sex;

quit;

Super Contributor
Posts: 301

Re: created a variable (sum new variable created) in the same proc sql

Thanks, but I would use the PGstats sugestion, it works fine.

Ask a Question
Discussion stats
  • 7 replies
  • 254 views
  • 0 likes
  • 4 in conversation