Hi everyone, there are anotehr way in proc sql to obtain the final value,
I've had to use another datasset new to get it, but i dunno if i can do it in the proc sql queries...thanks.
data have;
length leg sid 8 phase treattxt $10;
input sid leg phase $ treattxt $;
datalines;
1 100 active drug
1 100 off drug
2 101 active placebo
3 101 off placebo
3 101 active placebo
4 100 active drug
5 100 active drug
6 101 active placebo
6 101 off placebo
;
run;
proc sql noprint;
create table ntreat as
select count(distinct sid) as n, leg, treattxt
from have
where phase='active'
group leg, treattxt;
create table ntotal as
select count(distinct sid) as n, ' total' as treattxt,9999 as leg
from have
where phase='active';
quit;
data new;
set ntreat ntotal;
col0= ' ';
run;
Looks like you want to store a report layout in a table?
It's better to do the total in the report step, whatever how you so that?
SQL it's not suited to do tailored reports, therefore these three steps of yours.
nop, i only wanted avoid the last dataset, and try to calculate the total value in the same query, that's all.
What are you trying to get the total of? Its not clear from your post.
I just created and extratable called ntotal, to calculate the total subjects, and i dunno if I can calculate it, in the first query (table ntreat)
thanks.
create table ntotal as
select count(distinct sid) as n, ' total' as treattxt,9999 as leg
from have
where phase='active';
Again, storing totals is usually not best practice, it's a report thing.
But if you still think this is a good idea, just use insert into instead of create table.
proc sql noprint;
create table new as
select count(distinct sid) as n, leg, treattxt,' ' as col0
from have
where phase='active'
group leg, treattxt
;
insert into new
select count(distinct sid) as n, 9999 as leg,'total' as treattxt length=10,' ' as col0
from have
where phase='active'
;
quit;
I don't believe any of the standard SAS procs do a COUNT DISTINCT so you actually do need to store your total in a dataset in this case .
Sorry, what do you mean, can explain it better? thnaks.
I think she was simply agreeing with you that doing it in proc sql was a good approach.
You can use SQL's vertical union operator.
proc sql noprint;
create table want as
select count(distinct sid) as n, leg, treattxt
from have
where phase='active'
group leg, treattxt;
union all corr
select count(distinct sid) as n, ' total' as treattxt,9999 as leg
from have
where phase='active';
quit;
if you want COL0 be involved .use
outer union corr
Ksharp
Hi,
Can you try this below?
proc sql noprint;
create table new as
(select distinct put(leg,3.) as leg,treattxt, count(distinct sid) as cnt from have group by leg,treattxt having phase='active'
union all
select '',"total", count(distinct sid) from have where phase='active'
);
quit;
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.