Hi gus, my proc sql doesn't work, to get a final dataset with 7 variables, anyone can help me with this?
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,
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;
Thnaks,
V.
this is the only way I can do it. any other option?
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
from (select distinct subno,trt,time from have);
create table want2 as
select 'n subjects' as col0, sum(upcase(trt)='DRUG') as drug_total,
sum(upcase(trt)='PLACEBO') as pla_total
from (select distinct subno,trt from have);
quit;
data want3;
merge want want2;
by col0;
run;
What result do you want? - PG
want3, but, I was thinking to obtain want3 in the same query...something like that:
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
from (select distinct subno,trt,time from have);
*I am traing to join this two variables (drug_total and pla_total) for different select to the previous 5 ones to obtain a table (want3) with 7 variables;
select 'n subjects' as col0, sum(upcase(trt)='DRUG') as drug_total,
sum(upcase(trt)='PLACEBO') as pla_total
from (select distinct subno,trt from have);
quit;
How about other SAS tools :
proc freq data=have noprint;
format trt time $upcase.;
tables trt*time / out=h2Count;
tables trt / out=h1Count;
run;
proc sql;
create table lcount as
select * from h2count
union corr
select *, "TOTAL" as time from h1count;
drop table h1count, h2count;
quit;
proc transpose data=lcount out=want(drop=_:) delim=_;
var count;
id trt time;
run;
PG
any alternative way? thanks.
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.
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.