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;
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
Thank you very much, it works.
i need to click like Correct answer, but it does not appear at the moment in this discussion.
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;
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.
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
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;
Thanks, but I would use the PGstats sugestion, it works fine.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.