Dear all,
In my dataset, I have a varialbe with the observation values between 0 and 1, I want to count how many observations of this variable is blow 0.2, how many between 0.2 and 0.5, how many over 0.5. Can I have this done in one sql procedure? If yes, How?
Thanks
Select
sum(var<0.2) as lt_2,
sum(var>=0.2 and var <=0.5) as bet_2_5,
etc..
from have;
Yes you can. Sum the condition, if the condition is true it resolves to 1, 0 otherwise.
You could use
1.)the retain function in the data step and count the values using a series of if conditions or
2.)In proc sql ,probably use the Union option between three individual queries to obtain the counts with the help of group by statments
Select
sum(var<0.2) as lt_2,
sum(var>=0.2 and var <=0.5) as bet_2_5,
etc..
from have;
Yes you can. Sum the condition, if the condition is true it resolves to 1, 0 otherwise.
data want (keep=count_a count_b count_c);
set have end=done;
retain count_a count_a count_c 0;
if var < 0.2 then count_a + 1;
else if var <= 0.5 then count_b + 1;
else count_c + 1;
if done then output;
run;
Another method is to create a format, so the formatted values can be used in a simple freq:
proc format library=work;
value testfmt
low-<0.2 = 1
0.2-0.5 = 2
0.5<-high = 3
;
run;
data have;
input var;
format var testfmt.;
cards;
.1
.3
.6
.4
.5
.2
.7
;
run;
proc freq data=have order=internal;
tables var / nopercent nocum;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.