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

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

michtka — Tue, 19 Jun 2012 16:24:06 GMT

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;

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

PGStats — Tue, 19 Jun 2012 16:53:11 GMT

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;

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

Tom — Tue, 19 Jun 2012 16:55:03 GMT

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;

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

PGStats — Tue, 19 Jun 2012 16:58:57 GMT

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;

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

Reeza — Tue, 19 Jun 2012 17:07:44 GMT

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;

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

michtka — Wed, 20 Jun 2012 09:08:41 GMT

Thank you very much, it works.

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

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

michtka — Wed, 20 Jun 2012 09:27:47 GMT

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.

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

michtka — Wed, 20 Jun 2012 09:30:36 GMT

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