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

[InêsMaximiano]

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? 

[PaigeMiller]

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.

[InêsMaximiano]

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

[PaigeMiller]

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

2 2022 5 1 1

[InêsMaximiano]

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

[stew90210]

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

[InêsMaximiano]

Can you post an image of the code?

[stew90210]

[InêsMaximiano]

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

 

[stew90210]

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

[InêsMaximiano]

Do you think its easier to understand like this?

[stew90210]

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?

[InêsMaximiano]

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?!

[stew90210]

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

[stew90210]

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;