topic Re: How can I create a column that returns the number of months that have passed since a given event in SAS Data Management

How can I create a column that returns the number of months that have passed since a given event?

InêsMaximiano — Thu, 21 Jul 2022 10:03:32 GMT

I'm creating a table to show the pattern of my users.

For each month since they joined, I have an indicator (Flag) that tells me if on that particular month the user watched tv or not. If Flag = 1 it means they used it, and if Flag = 0 it means they didn't used it.

I need to create a new column that calculates for each month (each entry of the table) how many months have passed since the last time they watched tv.

Do you think you can help me write the code that creates this column?

Re: How can I create a column that returns the number of months that have passed since a given event

PaigeMiller — Thu, 21 Jul 2022 11:17:58 GMT

UNTESTED CODE

data want;
    set have;
    by userid;
    if first.user_id then months_since_last_event=0;
    if flag=1 then months_since_last_event=0;
    else if flag=0 then months_since_last_event+1;
run;

Since we can't write code to read in data that is part of a screen capture, I can't test this code. You need to provide data in a usable form. From now on, please provide code as a SAS data step which you can type in yourself, or by following these instructions, and not in any other format.

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 11:10:05 GMT

hey buddy,

not the tidiest code but I think this does what you want:

data input;
input USERID $1 Year Month Flag 4.;
datalines;
A 2021 12 0
A 2022 1 1
A 2022 2 1
A 2022 3 0
A 2022 4 0
A 2022 5 0
B 2021 8 0
B 2022 9 1
B 2022 10 1
B 2021 11 0
B 2021 12 0
B 2022 1 0
;
run;


data step1;
retain flag date_cat months_since_last_event;
set input;
by userID ;
prev_flag =lag(flag);

if first.userID then do;
months_since_last_event =  .;
end;

else if flag =0 and prev_flag =1 then do;
months_since_last_event=1;
end;

else if flag =1 then do;
months_since_last_event=0;
end;


else do;
months_since_last_event+1;
end;
drop prev_flag;
run;

Re: How can I create a column that returns the number of months that have passed since a given event

InêsMaximiano — Thu, 21 Jul 2022 11:27:21 GMT

Thanks for your fast reply! I tried your code and it didnt work because I need it to restart everytime Flag = 1.

Sorry, I didn't provide data in a usable form, I just wrote an example because I still don't have the exact data, but it would be something like this:

data have;
input UserID Year Month Flag;
datalines;
1 2021 10 0
1 2021 11 0
1 2021 12 1
1 2022 1 1
1 2022 2 0
1 2022 3 1
1 2022 4 0
2 2021 1 0
2 2021 2 0
2 2021 3 1
2 2021 4 1
2 2022 5 1
2 2022 6 0
;

I was also told I should use the function mdy to join the month and year of each line creating and auxiliar column and then use this to calculate the difference of months between each event (flag =1).

I think the output should look like this:

UserID Year Month Flag Months_since_event
1 2021 10 0 .
1 2021 11 0 .
1 2021 12 1 0
1 2022 1 1 1
1 2022 2 0 1
1 2022 3 1 2
1 2022 4 0 1
2 2021 1 0 .
2 2021 2 0 .
2 2021 3 1 0
2 2021 4 1 1
2 2022 5 1 1
2 2022 6 0 1

Re: How can I create a column that returns the number of months that have passed since a given event

PaigeMiller — Thu, 21 Jul 2022 11:34:35 GMT

Why does this line not have a 2 in the last column? Please explain.

2 2022 5 1 1

Re: How can I create a column that returns the number of months that have passed since a given event

InêsMaximiano — Thu, 21 Jul 2022 11:48:34 GMT

2 2021 3 1 0
2 2021 4 1 1
2 2022 5 1 1

---> In the first line its 0 because its the first time for that user that Flag =1
----> in the second line its 1 because its been 1 month since flag =1
----> in the third line (the one you questioned me about) its 1 again because its been 1 month since flag =1, it would be only 2 if the flag in the month before was 0 like this:

2 2021 3 1 0
2 2021 4 1 1
2 2022 5 0 1
2 2022 6 0 2

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 13:34:38 GMT

I have the code you want but every time I post it, it gets marked as SPAM for some reason!

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 14:11:12 GMT

data input;
input USERID $1 Year Month Flag 4.;
datalines;
A 2021 12 0
A 2022 1 1
A 2022 2 1
A 2022 3 0
A 2022 4 0
A 2022 5 0
B 2021 8 0
B 2022 9 1
B 2022 10 1
B 2021 11 0
B 2021 12 0
B 2022 1 0
;
run;


data step1;
retain flag date_cat months_since_last_event;
set input;
by userID ;
prev_flag =lag(flag);

if first.userID then do;
months_since_last_event =  .;
end;

else if flag =0 and prev_flag =1 then do;
months_since_last_event=1;
end;

else if flag =1 then do;
months_since_last_event=0;
end;


else do;
months_since_last_event+1;
end;
drop prev_flag;
run;

data input;
input USERID $1 Year Month Flag 4.;
datalines;
A 2021 12 0
A 2022 1 1
A 2022 2 1
A 2022 3 0
A 2022 4 0
A 2022 5 0
B 2021 8 0
B 2022 9 1
B 2022 10 1
B 2021 11 0
B 2021 12 0
B 2022 1 0
;
run;

data step1;
retain flag date_cat months_since_last_event;
set input;
by userID ;
prev_flag =lag(flag);
if first.userID then do;
months_since_last_event =  .;
end;
else if flag =0 and prev_flag =1 then do;
months_since_last_event=1;
end;
else if flag =1 then do;
months_since_last_event=0;
end;
else do;
months_since_last_event+1;
end;
drop prev_flag;
run;

Re: How can I create a column that returns the number of months that have passed since a given event

InêsMaximiano — Thu, 21 Jul 2022 14:19:37 GMT

Can you post an image of the code?

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 14:21:11 GMT

Re: How can I create a column that returns the number of months that have passed since a given event

InêsMaximiano — Thu, 21 Jul 2022 14:41:31 GMT

Thanks for your time! However when I wrote the code you gave me I obtained the following output:

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 15:10:19 GMT

sorry but that doesnt match what i get as an output and also your "coloumn I want" dosent make logical sense to me? in jan, feb and march of 22 user ID 1's results dont make any sense

Re: How can I create a column that returns the number of months that have passed since a given event

InêsMaximiano — Thu, 21 Jul 2022 15:22:14 GMT

Do you think its easier to understand like this?

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 15:23:03 GMT

with regards to user ID 1; why is march 22 a 2 instead of a 0? if dec 21 is a 0 then shouldn't it follow that it is the same? if you are trying to show how long its been since the last time they watched TV then surely whenever they are actually watching TV it should be a 0 to say its been no months since the last instance?

Re: How can I create a column that returns the number of months that have passed since a given event

InêsMaximiano — Thu, 21 Jul 2022 15:29:18 GMT

Yeah I understand what you are saying and I had the same doubt... I was not sure if it was best to put zero on the month they are watching tv or just showing how many months it has been... Because from watching the table we know they watched tv on that month, but we don't see how months have passed since the last time they did it?!

Re: How can I create a column that returns the number of months that have passed since a given event

stew90210 — Thu, 21 Jul 2022 16:30:12 GMT

Ok well hopefully you have enough to get your started in my code! Come back if you get stuck