topic Re: What's wrong with the new field 'Sex'? in SAS Procedures

What's wrong with the new field 'Sex'?

zhangda — Wed, 16 Nov 2016 18:36:07 GMT

Hello,

I am coding a new field 'sex', for the table 'grade', but why the value is all 'N' but not 'Y', because I insert the value of resp1 into 'Y', which is not supposed to be null. Please guide me. Thanks!

data ds1;

input year $ mid $ resp1 resp2;

mid2=cat(strip(mid), "_2");

datalines;

null d_2016 0.45 0.64

null d_2015 0.35 0.783

s_300 d_2016 0.65 0.67

s_300 d_2015 0.55 0.75

s_640 d_2016 0.42 0.89

s_640 d_2015 0.65 0.87

s_670 d_2016 0.35 0.12

s_670 d_2015 0.75 0.09

s_700 d_2016 0.83 0.06

s_700 d_2015 0.47 0.07

s_730 d_2016 0.48 0.18

s_730 d_2015 0.58 0.21

s_760 d_2016 0.67 0.05

s_760 d_2015 0.69 0.045

;

run;

proc sql;

create table grade as

select * ,

case when resp2 <>0 then 'N'

when resp1 <>0 then 'Y'

else 'Other' end as Sex

from ds1; quit;

Re: What's wrong with the new field 'Sex'?

Reeza — Wed, 16 Nov 2016 18:53:50 GMT

Case statements are evaluated in the order they appear.

Since your first condition is always true you never reach the second condition.

Re: What's wrong with the new field 'Sex'?

ballardw — Wed, 16 Nov 2016 18:59:05 GMT

This statement is true for all of your example data

resp2 <>0

so the evaluation of that line sets everything to "N"

If you are using SAS you want to think "missing" instead of "null". SAS has specific functions to deal with missing and does not use the concept "null".

Also you use <> which SAS is going to treat as "return the maximum value of the two values compared" and not what I believe you meant by "not equal". And SAS treats any non-zero value that is not missing as "True".

Re: What's wrong with the new field 'Sex'?

Reeza — Wed, 16 Nov 2016 19:00:36 GMT

SQL treats <> as not equals

Data step (not Where) treats it as maximum