topic Re: Update new column based on three other columns in New SAS User

Update new column based on three other columns

SA13 — Fri, 06 Nov 2020 02:13:37 GMT

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

Re: Update new column based on three other columns

tomrvincent — Fri, 06 Nov 2020 13:12:49 GMT

What have you tried so far? Provide your code.

Re: Update new column based on three other columns

ballardw — Fri, 06 Nov 2020 15:24:05 GMT

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.

Re: Update new column based on three other columns

SA13 — Sat, 07 Nov 2020 12:13:35 GMT

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.

Re: Update new column based on three other columns

Kurt_Bremser — Sat, 07 Nov 2020 13:31:58 GMT

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;

Re: Update new column based on three other columns

Kurt_Bremser — Sat, 07 Nov 2020 13:34:39 GMT

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.

Re: Update new column based on three other columns

SA13 — Mon, 09 Nov 2020 01:35:35 GMT

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.

Re: Update new column based on three other columns

Kurt_Bremser — Mon, 09 Nov 2020 07:09:10 GMT

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

Re: Update new column based on three other columns

SA13 — Mon, 09 Nov 2020 10:06:12 GMT

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.

Re: Update new column based on three other columns

Kurt_Bremser — Mon, 09 Nov 2020 10:14:09 GMT

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.

Re: Update new column based on three other columns

SA13 — Mon, 09 Nov 2020 10:30:05 GMT

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.

Re: Update new column based on three other columns

Kurt_Bremser — Mon, 09 Nov 2020 11:02:44 GMT

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.

Re: Update new column based on three other columns

SA13 — Mon, 09 Nov 2020 15:17:35 GMT

Thank you so much for your help. It worked.