topic Re: How to conditionally count observations of variable into several different new variables with in SAS Programming

How to conditionally count observations of variable into several different new variables with sql

owenwqp1 — Thu, 18 Aug 2016 05:37:14 GMT

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

Re: How to conditionally count observations of variable into several different new variables with

amanda4286 — Thu, 18 Aug 2016 05:55:05 GMT

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

Re: How to conditionally count observations of variable into several different new variables with

Reeza — Thu, 18 Aug 2016 06:31:35 GMT

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.

Re: How to conditionally count observations of variable into several different new variables with

owenwqp1 — Thu, 18 Aug 2016 06:54:59 GMT

Thank you very much Reeza! It works!

Re: How to conditionally count observations of variable into several different new variables with

Kurt_Bremser — Thu, 18 Aug 2016 07:05:04 GMT

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;