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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.