topic Re: how to combine several proc sql to one in SAS Programming

how to combine several proc sql to one

dan999 — Fri, 10 Aug 2012 18:45:43 GMT

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

Re: how to combine several proc sql to one

Reeza — Fri, 10 Aug 2012 20:37:58 GMT

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;

Re: how to combine several proc sql to one

dan999 — Fri, 10 Aug 2012 21:13:38 GMT

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

Re: how to combine several proc sql to one

Patrick — Sat, 11 Aug 2012 09:22:58 GMT

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.

Re: how to combine several proc sql to one

Howles — Sat, 11 Aug 2012 14:09:16 GMT

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