Hi
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;
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);
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".
Hi
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;
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);
Thanks so much! Great.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.