BookmarkSubscribeRSS Feed
dan999
Fluorite | Level 6

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

4 REPLIES 4
Reeza
Super User

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
Fluorite | Level 6

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
Opal | Level 21

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
Quartz | Level 8

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 825 views
  • 0 likes
  • 4 in conversation