I currently have several proc sql statements like these:
proc sql;
create table dm1 as
select
'HBA' as program,
count(distinct id_prg) as cnt
from ql_dm
where prgid in ('01')
group by program;
quit;
I might have 5 or so of those with different where clauses. I was thinking that it was possible to do something like:
proc sql;
create table dm_all as
select 'Measurements for today',
count(case when prgid in ('01') then distinct id_prg else 0 end) as measure1,
....
from ql_dm
quit;
Is that possible?
By the way, is it possible to cut and paste text into this message? I haven't figured out how to yet.
Thanks
You can.
I often do something like this when coming up with summary tables and then transpose the results.
proc sql;
create table summary1 as
select sum(sex='F') as num_female,
sum(sex='M') as num_male,
sum(age<13) as num_preteens,
sum(age>=13) as num_teens
from sashelp.class;
quit;
Yes, I've done things like that too. I don't have a problem with the sum. I can also do count execpt in cases where I'm trying to do a distinct count.
Thanks
May be I don't understand your problem - but isn't this just about how you set-up your group by clause?
data ql_dm(drop=_i:);
do program='HBA','HBB';
do prgid='01','02','03';
do _i=1 to 5;
id_prg=input(prgid,8.) *_i;
output;
output;
if prgid='02' then do;id_prg=input(prgid,8.) *_i*_i; output; end;
end;
end;
end;
run;
proc sql;
/* create table dm1 as*/
select
program,
prgid,
count(distinct id_prg) as cnt
from ql_dm
where prgid in ('01','02')
group by program,prgid
;
quit;
About "cut-and-paste": I always paste first from SAS EG to Notepad++ and then from Notepad++ to the forum.
To copy code out of the forum: I copy it into MS Word, then into Notepad, then into SAS EG - this way I get rid of all unwanted stuff.
Two things are needed:
1. Change the zero to a missing values, because zeroes count.
2. Move DISTINCT immediately after the opening parenthesis of the summary function (COUNT). That's a syntax rules and besides, having it inside the CASE makes no sense because the CASE rules are applied one row at a time.
Example:
proc sql ;
create table dm_all as
select sex,
count(distinct case when name EQT 'J'
then age
else (.) end) as measure1,
count(distinct case when length(name) EQ 4
then age
else (.) end) as measure2
from sashelp.class
group by sex ;
quit;
dan999 wrote:
I currently have several proc sql statements like these:
proc sql;
create table dm1 as
select
'HBA' as program,
count(distinct id_prg) as cnt
from ql_dm
where prgid in ('01')
group by program;
quit;
I might have 5 or so of those with different where clauses. I was thinking that it was possible to do something like:
proc sql;
create table dm_all as
select 'Measurements for today',
count(case when prgid in ('01') then distinct id_prg else 0 end) as measure1,
....
from ql_dm
quit;
Is that possible?
By the way, is it possible to cut and paste text into this message? I haven't figured out how to yet.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.