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;
Remove SUM() from your PROC SQL. The SUM works over the entire data set, whereas you want the comparison to be on each row.
Remove SUM() from your PROC SQL. The SUM works over the entire data set, whereas you want the comparison to be on each row.
No need for proc sql, just use a data step:
data want;
set a;
l_outl_vol = var1 < l_outlier;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.