topic Re: Proc SQL count with missing values in SAS Programming

Proc SQL count with missing values

Toni2 — Tue, 28 Jun 2022 09:38:14 GMT

I try to count var1 when it is less than l_outlier however, the proc sql returns 1 in all cases

this is table b

var1	l_outlier	l_outl_vol
1	5	1
2	1	1
3		1
4		1
5		1

i want the below. This is count var1 when it is less than l_outlier and not take in consideration the missing values

var1	l_outlier	l_outl_vol
1	5	1
2	1	0
3		0
4		0
5		0

data a;
    input var1 l_outlier;
    datalines;
1 5
2 1
3 .
4 .
5 .
;
run;

proc sql;
       create table b  as
       select *,
	   sum (case when  var1 < l_outlier then 1 else 0 end) as l_outl_vol
	   from a;
               quit;

Re: Proc SQL count with missing values

PaigeMiller — Tue, 28 Jun 2022 09:50:26 GMT

Remove SUM() from your PROC SQL. The SUM works over the entire data set, whereas you want the comparison to be on each row.

Re: Proc SQL count with missing values

andreas_lds — Tue, 28 Jun 2022 11:54:10 GMT

No need for proc sql, just use a data step:

data want;
   set a;
   l_outl_vol = var1 < l_outlier;
run;