topic Re: Create new variable based on threshold in SAS Data Management

Create new variable based on threshold

Oalmend — Mon, 02 Oct 2017 18:53:52 GMT

I need to combine multiple dichotomous variables into one but my code is not giving the correct output.

data cms_st2;
set cms_st;


If Linked=1 and then Status=1; 
If Eligible=1 then Status=2;
If Linked=0 and Eligible=0 then Status=0;

run;

The output this gives me is:

Status Frequency Cumulative
Frequency 0 1 2

827	827
272	1099
493	1592

But Status=1 (i.e. Linked=1, regardless of whether Eligible=1 or 0) should be 765

Re: Create new variable based on threshold

Astounding — Mon, 02 Oct 2017 19:40:56 GMT

Your statements execute in order. So if you assign STATUS a value of 1, it's entirely possible that the next statement changes that and assigns STATUS a value of 2. Perhaps this is what you meant for the first two statements:

if Linked=1 then Status=1;

else if Eligible=1 then Status=2;

Re: Create new variable based on threshold

PGStats — Mon, 02 Oct 2017 19:41:57 GMT

If Linked=1 and then Status=1;

is incorrect syntax.

Re: Create new variable based on threshold

ballardw — Mon, 02 Oct 2017 19:55:32 GMT

You really want to consider using an ELSE.

If your code was actually supposed to be :

If Linked=1 then Status=1;
If Eligible=1 then Status=2;
If Linked=0 and Eligible=0 then Status=0;

the result for Linked=1 Eligible=1 would be Status=2 because the second if is evaluated after the first assignment.

Perhaps:

If Linked=1 then Status=1;
ELSE If Eligible=1 then Status=2;
Else if Linked=0 and Eligible=0 then Status=0;

Re: Create new variable based on threshold

Oalmend — Tue, 03 Oct 2017 13:42:54 GMT

when I run

data cms_st2;
set cms_st;


If Linked=1 then Status=1; 
else If Eligible=1 then Status=2;
else If Linked=0 and Eligible=0 then Status=0;
run;

then it only assigns values for status 0 and 1, 2 is ignored.

Re: Create new variable based on threshold

ballardw — Tue, 03 Oct 2017 15:46:58 GMT

Show some of your data.

This example code exists all the possibilities of dichotomous non-missing variables coded 0 and 1 and has results of 0, 1 and 2.

data example;
input linked Eligible;

If Linked=1 then Status=1; 
else If Eligible=1 then Status=2;
else If Linked=0 and Eligible=0 then Status=0;
datalines;
0 0
1 0
0 1
1 1
;
run;

If your are not getting 1 or 2 in your data then examine your log and see if you are getting any warnings

or run proc freq on your data:

Proc freq data=cms_st;

tables linked*Eligible / list;

run;

and see what values are actually in your data.