Using SAS 9.4
I have numeric data and I am trying to count across multiple columns to get the total number of columns per row that have a non 0/non missing (.) number.
An example of my data is below. As you can see I can have 0 or . and I do not want to add those to my count. SO for the example below the count would be 8.
sl_mcpjoints | sl_thump_mcp | sl_wrist | sl_wrist_4cm | sl_wrist_8cm | sl_wrist_12cm | sl_wrist_16cm | sl_wrist_20cm | sl_wrist_24cm | sl_wrist_28cm | sl_wrist_32cm |
18.5 | 18.8 | 15.2 | 14.8 | 16.7 | 20.5 | 22.9 | 24.2 | 0 | . | 0 |
I am running the following code but it is producing a count of 11. Any thoughts on what I am doing incorrectly? Thank you
data test;
set have;
if sl_MCPjoints >0 then sl_W_count_1 = 1;
if sl_thump_mcp >0 then sl_W_count_2 = 1;
if sl_wrist >0 then sl_W_count_3 = 1;
if sl_wrist_4cm >0 then sl_W_count_4 = 1;
if sl_wrist_8cm >0 then sl_W_count_5 = 1;
if sl_wrist_12cm >0 then sl_W_count_6 = 1;
if sl_wrist_16cm >0 then sl_W_count_7 = 1;
if sl_wrist_20cm >0 then sl_W_count_8 = 1;
if sl_wrist_24cm >0 then sl_W_count_9 = 1;
if sl_wrist_28cm >0 then sl_W_count_10 = 1;
if sl_wrist_32cm >0 then sl_W_count_11 = 1;
if sl_elbow_crease >0 then sl_E_count_12 = 1;
if sl_elbow_4cm >0 then sl_E_count_13 = 1;
if sl_elbow_8cm >0 then sl_E_count_14 = 1;
if sl_elbow_12cm >0 then sl_E_count_15 = 1;
if sl_elbow_16cm >0 then sl_E_count_16 = 1;
if sl_elbow_20cm >0 then sl_E_count_17 = 1;
if sl_elbow_24cm >0 then sl_E_count_18 = 1;
if sl_elbow_28cm >0 then sl_E_count_19 = 1;
if sl_elbow_32cm >0 then sl_E_count_20 = 1;
PRE_SL_W_count = (sl_W_count_1 + sl_W_count_2 + sl_W_count_3 + sl_W_count_4 + sl_W_count_5 + sl_W_count_6 + sl_W_count_7 + sl_W_count_8 + sl_W_count_9 + sl_W_count_10 + sl_W_count_11);
PRE_SL_E_count = (sl_E_count_12 + sl_E_count_13 + sl_E_count_14 + sl_E_count_15 + sl_E_count_16 + sl_E_count_17 + sl_E_count_18 + sl_E_count_19 + sl_E_count_20);
run;
Do you need all of those flag variables also? Or just the count?
Did you mean you want to count the number of positive values? That is what your code is doing. But your original problem statement was you wanted to count non-zero,non-missing values. Is it possible the value is ever negative? If so is that the same as zero or missing?
Because of the need to exclude zero values you will probably want to use an array statement to simplify the coding.
If you just want to test for not zero and not missing then treat the value as a boolean. SAS evaluates any non-zero value as TRUE and anything else (zero or missing) as FALSE.
data test;
set have;
array si
sl_MCPjoints
sl_thump_mcp
sl_wrist
sl_wrist_4cm
sl_wrist_8cm
sl_wrist_12cm
sl_wrist_16cm
sl_wrist_20cm
sl_wrist_24cm
sl_wrist_28cm
sl_wrist_32cm
sl_elbow_crease
sl_elbow_4cm
sl_elbow_8cm
sl_elbow_12cm
sl_elbow_16cm
sl_elbow_20cm
sl_elbow_24cm
sl_elbow_28cm
sl_elbow_32cm
;
PRE_SL_W_count=0;
do index=1 to dim(SI);
if si[index] then PRE_SL_W_count = PRE_SL_W_count+1;
end;
run;
Use an array:
data test;
set have;
array _nums {*} _numeric_;
PRE_SL_W_count = 0;
do _i = 1 to dim(_nums);
if nums{_i} not in (0,.) then PRE_SL_W_count = PRE_SL_W_count + 1;
end;
drop _i;
run;
If you have variables that should not be tested, you need to use an explicit variable list in the array statement.
When you want to perform the same operations across many columns, use arrays
Something like this
data test;
set have;
array x sl_wrist--sl_wrist_32cm;
w_count=0;
do i=1 to dim(x);
if x(i)>0 then w_count=w_count+1;
end;
drop i;
run;
You can modify the code to add in e_count.
Do you need all of those flag variables also? Or just the count?
Did you mean you want to count the number of positive values? That is what your code is doing. But your original problem statement was you wanted to count non-zero,non-missing values. Is it possible the value is ever negative? If so is that the same as zero or missing?
Because of the need to exclude zero values you will probably want to use an array statement to simplify the coding.
If you just want to test for not zero and not missing then treat the value as a boolean. SAS evaluates any non-zero value as TRUE and anything else (zero or missing) as FALSE.
data test;
set have;
array si
sl_MCPjoints
sl_thump_mcp
sl_wrist
sl_wrist_4cm
sl_wrist_8cm
sl_wrist_12cm
sl_wrist_16cm
sl_wrist_20cm
sl_wrist_24cm
sl_wrist_28cm
sl_wrist_32cm
sl_elbow_crease
sl_elbow_4cm
sl_elbow_8cm
sl_elbow_12cm
sl_elbow_16cm
sl_elbow_20cm
sl_elbow_24cm
sl_elbow_28cm
sl_elbow_32cm
;
PRE_SL_W_count=0;
do index=1 to dim(SI);
if si[index] then PRE_SL_W_count = PRE_SL_W_count+1;
end;
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!
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.