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?
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.
Why does this line not have a 2 in the last column? Please explain.
2 2022 5 1 1
Thanks for your time! However when I wrote the code you gave me I obtained the following output:
Do you think its easier to understand like this?
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.