BookmarkSubscribeRSS Feed
michtka
Fluorite | Level 6

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;

7 REPLIES 7
PGStats
Opal | Level 21

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
michtka
Fluorite | Level 6

Thank you very much, it works.

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

Tom
Super User Tom
Super User

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;

michtka
Fluorite | Level 6

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.

PGStats
Opal | Level 21

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
Reeza
Super User

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;

michtka
Fluorite | Level 6

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

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
  • 7 replies
  • 926 views
  • 0 likes
  • 4 in conversation