topic sql to sas in New SAS User

sql to sas

jorquec — Wed, 02 Sep 2020 22:11:33 GMT

I know easily how to do this code in Sql but how to do this count and case when on SAS statement?

Could someone give me some help?

Sql code:

Select count( subscr_id) as qty,
Case when m3_topup_index =0 THEN 'a.Zero'
when m3_topup_index > 0 and m3_topup_index<=0.5 THEN 'b.0-0.5'
when m3_topup_index > 0.5 and m3_topup_index<=1 THEN 'c.0.5-1'
when m3_topup_index > 1 and m3_topup_index<=1.5 THEN 'd.1- 1.5'
when m3_topup_index > 1.5 and m3_topup_index<=2.0 THEN 'e.1.5-2.0'
when m3_topup_index > 2.0 THEN 'f.Above 2.0'
else 'g.Other'
end as m3_topup_index_grp

/* The variable m3_topup_index_grp is not on my SAS dataset.

So how do I create it as I only have the variable m3_topu_index */

from ptwopperfs_1_may20 /* my sas dataset*/
group by subscr_id

I have tried this on SAS but is not correct:

proc summary data=perfmoni.ptwopperfs_1_may20 nway;

var m3_topup_index;

output out=want (drop=_:)

n=count_subscr_id
sum=sum_m3_topup_index;

run;

Re: sql to sas

novinosrin — Wed, 02 Sep 2020 22:48:41 GMT

Hi @jorquec Please take a look at "Proc Format" to defne the ranges and the associated labels for the ranges. Then apply/call the format in "Proc Summary".

Re: sql to sas

jorquec — Thu, 03 Sep 2020 14:07:14 GMT

thanks for the tip!

Could you please tell me how to add now an additional information of the frequency but in percentage ?

/* step1*/
DATA work.counts;
set perfmoni.ptwopperfs_1_may20;

length m3_topup_index_grp $ 30;
format m3_topup_index_grp  $char.;

If m3_topup_index =0 THEN m3_topup_index_grp='a.Zero';
else if m3_topup_index > 0 and  m3_topup_index<=0.5  THEN m3_topup_index_grp= 'b.0-0.5';
else if m3_topup_index > 0.5 and  m3_topup_index<=1  THEN m3_topup_index_grp='c.0.5-1';
else if m3_topup_index > 1 and  m3_topup_index<=1.5  THEN m3_topup_index_grp= 'd.1- 1.5';
else if m3_topup_index > 1.5 and  m3_topup_index<=2.0  THEN m3_topup_index_grp= 'e.1.5-2.0';
else if m3_topup_index > 2.0 THEN m3_topup_index_grp= 'f.Above 2.0';
else  m3_topup_index_grp= 'g.Other';
run;

/* step 2*/
proc summary data=work.counts ;
types m3_topup_index_grp ;   
  class m3_topup_index_grp ;
  var   subscr_id;
  output out=total_subscrid n(subscr_id)= 
        / autoname autolabel;
run;

Re: sql to sas

Tom — Thu, 03 Sep 2020 14:26:03 GMT

I would use a format, then you don't need to modify your data.

proc format ;
  value index_grp 
        0 = 'a.Zero'
   0<-0.5 = 'b.0-0.5'
   0.5<-1 = 'c.0.5-1'
   1<-1.5 = 'd.1- 1.5'
   1.5<-2 = 'e.1.5-2.0'
  2<-high = 'f.Above 2.0'
    other = 'g.Other'
  ;
run;

To get count and percent use PROC FREQ.

proc freq data=ptwopperfs_1_may20 ;
  tables m3_topup_index / missing;
  format m3_topup_index index_grp. ;
run;

If you want to eliminate the observations with missing values of subscr_id like the COUNT(subscr_id) aggregate function in SQL will do then add a where statement.

where not missing(subscr_id);

Re: sql to sas

jorquec — Fri, 04 Sep 2020 13:50:14 GMT

Thanks so much! Great.