Update new column based on three other columns

[SA13]

My data set has "subject", "Visit", "Adverse_Event_Start", and  "Adverse_Ever_End". 

I need to create a new column "Advse_Event_Present" based on "Subjec", "Visit", ""Adverse_Event_Start", and  "Adverse_Ever_End". 

In "Adverse_Event_Start" column:  "1" means "adverse event started"

In  "Adverse_Event_End" column:  "1" means "adverse event ended"

The zero values in both columns are not informative.

The "Adverse_Event_Present" are created as following:

If Adverse_Event_Start= 1 and Adverse_Event_End=0, the "Adverse_Event_Present" is 1.

If Adverse_Event_Start= 0 and Adverse_Event_End=1, the "Adverse_Event_Present" is 0.

If Adverse_Event_Start= 1 and Adverse_Event_End=1, the "Adverse_Event_Present" is 1.

If Adverse_Event_Start= 0 and Adverse_Event_End=0, the "Adverse_Event_Present" can be "0" or "1" based on previous visit (s) for each subject.

For example, for subject= A, Visit=3, the previous visit for the same subject (subject= A, Visit=2) has Adverse_Event_Start=1 and Adverse_Event_End  =0, therefore Adverse_Event_Present (for subject= A, Visit=3) should be 1.

 Below, I fill out the true values for “Adverse_Event_Present” if the program works properly.

Any help will be greatly appreciated.

Subject      Visit      Adverse_Event_Start  Adverse_Event_End   Adverse_Event_Present

A                 1                     0                                          0                               0

A                 2                      1                                         0                                1

A                3                       0                                         0                                 1

A                 4                       0                                         1                                 0

A                  5                       0                                         0                                0

B                 1                      0                                          0                                0

B                 2                      1                                          0                                1

B                  3                      0                                         0                                 1

B                  4                      0                                         1                                  0

C                 1                       1                                         0                                  1

C                 2                       0                                         0                                   1

C                 3                        0                                         0                                  1

C                 4                        0                                         0                                   1

D                  1                       1                                          1                                  1

D                   2                      0                                          0                                  0

D                    3                      0                                          0                                  0

[tomrvincent]

What have you tried so far? Provide your code.

[ballardw]

The data you show and result described means the result is completely dependent on Adverse_Event_Start and it doesn't matter what the other variable has as a value:

If Adverse_Event_Start =1 then Adverse_Event_Start=1;

else Adverse_Event_Start=0;

Or you data description is incomplete and you actually have other values and/or missing values for one of the variables. In which case you need to provide rules with those.

[SA13]

Thank you for reading my post.

Your answer is not accurate.

The adverse event depends on both start date and end date. Also, some
subjects just has adverse event start date which means in all next visits
the adverse event is equal to 1. I am trying to figure out how to write a
proper code.

[Kurt_Bremser]

If you want us to test our code, supply example data in usable form, which is a working data step with datalines that creates the dataset as you have it.

Use the "little running man" button to post the code.

[Kurt_Bremser]

Hi @ballardw I think your condition needs to be expanded a little:

retain adverse_event_present;
l_end = lag(adverse_event_end);
If Adverse_Event_Start
then Adverse_Event_present = 1;
else if adverse_event_end or l_end
then Adverse_Event_present = 0;
drop l_end;

[SA13]

I still did not get what I wanted. Let me explain better. Here is my data:

data New_data;
input subject$ visit adverse_event_start adverse_event_end;
datalines;
A 1 0 0
A 2 1 0
A 3 0 0
A 4 0 1
A 5 0 0
B 1 0 0
B 2 1 0
B 3 0 0
B 4 0 1
c 1 1 0
c 2 0 0
c 3 0 0
c 4 0 0
D 1 1 1
D 2 0 0
D 3 0 0
;
run;



[image: image.png]

I want to create a new column "adverse_event_present" based on subject,
visit, adverse_event_start, and adverse_event_end.

The final result should be like this

[image: image.png]

I tried the following code based on the provided hint:

Data New_Cat;
set New_data;
retain adverse_event_present;
l_end=lag(advrese_event_end);
if adverse_event_start then adverse_event_present=1;
else if advrese_event_end or l_end then adverse_Event_present = 0;
drop l_end;
run;

However, the output is not what I wanted:

[image: image.png]

Any help is greatly appreciated.

[Kurt_Bremser]

Could you please repost your expected result? Use the "camera" button to post pictures.

[SA13]

Sure.

I still did not get what I wanted. Let me explain better. Here is my data:

data New_data;
input subject$ visit adverse_event_start adverse_event_end;
datalines;
A 1 0 0
A 2 1 0
A 3 0 0
A 4 0 1
A 5 0 0
B 1 0 0
B 2 1 0
B 3 0 0
B 4 0 1
c 1 1 0
c 2 0 0
c 3 0 0
c 4 0 0
D 1 1 1
D 2 0 0
D 3 0 0
;
run;



I want to create a new column "adverse_event_present" based on subject,
visit, adverse_event_start, and adverse_event_end.

The final result should be like this



I tried the following code based on the provided hint:

Data New_Cat;
set New_data;
retain adverse_event_present;
l_end=lag(advrese_event_end);
if adverse_event_start then adverse_event_present=1;
else if advrese_event_end or l_end then adverse_Event_present = 0;
drop l_end;
run;

However, the output is not what I wanted:



Any help is greatly appreciated.

[Kurt_Bremser]

You have a typo that creates (and tests!) an unwanted variable:

else if advrese_event_end

this unwanted variable is always missing, so the condition will always be false.

This can also be seen in the log ("variable is uninitialized"). A NOTE you do not want to have in your logs.

[SA13]

Oh, you are right. 

Data New_Cat;
set New_data;
retain adverse_event_present;
l_end=lag(adverse_event_end);
if adverse_event_start then adverse_event_present=1;
else if adverse_event_end or l_end then adverse_Event_present = 0;
drop l_end;
run;

The output is 

The first value of " adverse_Event_present" is missing. It should be "0". The other values are exactly what I wanted. 

[Kurt_Bremser · Accepted solution]

We need to add logic for group changes. Make sure that the dataset is properly sorted, and add these statements:

by subject;
if first.subject then adverse_event_present = 0;

before all the other logic that sets the new variable.

[SA13]

Thank you so much for your help. It worked.