replacing observations in sas

2
votes

I would like to assign to some of the observations in the "ondays" variable below new values that depend on whether the variable "failure" is 0 or 1. Here my data set

    ID         X       ONDAYS       FAILURE
    1          0         59          1
    2          0        514          1
    3          0        313          0
    4          0        631          1
    5          0        107          1
    6          0         71          0
    7          0        583          1
    8          0         91          1
    9          0         66          1
   10          0         95          0

My goal is to sort ONDAYS---from lowest to highest (and I can do this)---to order values in ONDAYS and then create a new variable called "NEWDAYS" which will have the same value as in ONDAYS if FAILURE=1, but if FAILURE=0, NEWDAYS will be equal to closest (lower value) that correspond to a FAILURE=1 value in ONDAYS. For example in NEWDAYS observation 1 will equal to 59, but observation 6 will be equal to 66 (since the closest ordered "failure=1" value to 71 is 66). Can you please help me how to do this?

Thanks.

Roland

1
sas

1 Answers

2
votes

Like this?

data have;
input ID         X       ONDAYS       FAILURE;
datalines;
    1          0         59          1
    2          0        514          1
    3          0        313          0
    4          0        631          1
    5          0        107          1
    6          0         71          0
    7          0        583          1
    8          0         91          1
    9          0         66          1
   10          0         95          0
   ;;;;
run;
proc sort data=have;
by ondays;
run;
data want;
set have;
by ondays;
retain prev_ondays;
if failure=0 then new_ondays=prev_ondays;
else new_ondays=ondays;
output;
prev_ondays=new_ondays;
run;

Just keep track of the previous ONDAYS and assign it to the new ONDAYS if needed.

Another option: SQL. This updates the current table, which might be desired and might not; if not, create a new table and apply this.

proc sql undopolicy=none;
update have H set ondays=(select max(ondays) from have V where H.ondays ge V.ondays and V.failure=1)
where failure=0;
quit;