how to combine several proc sql to one

[dan999]

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

[Reeza]

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;

[dan999]

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

[Patrick]

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.

[Howles]

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