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:

procsql ;

createtable 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

groupby 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.