Hi all,
I have a large dataset with ID, month and 1p_flag (1 or 0) as shown below.
I am trying to create a flag by ID (variable '1_to_0'_to_1), so that for each month I can see if the flag has moved from 1 to 0 to 1 looking at historical months.
I then need to work out the months in between 1 to 0 to 1.
How can I best code this?
I hope this example shows where I want to get to.
Thanks in advance.
ID | Month | 1p_flag | 1_to_0_to_1 | months_between |
1 | Mar-12 | 0 | ||
1 | Apr-12 | 0 | ||
1 | May-12 | 1 | ||
1 | Jun-12 | 1 | ||
1 | Jul-12 | 0 | ||
1 | Aug-12 | 0 | ||
1 | Sep-12 | 1 | 1 | 3 |
1 | Oct-12 | 0 | ||
2 | Apr-12 | 1 | ||
2 | May-12 | 0 | ||
2 | Jun-12 | 0 | ||
2 | Jul-12 | 1 | 1 | 3 |
2 | Aug-12 | 0 | ||
2 | Sep-12 | 0 | ||
2 | Oct-12 | 0 | ||
2 | Nov-12 | 1 | 1 | 4 |
2 | Dec-12 | 0 | ||
2 | Jan-13 | 1 | 1 | 2 |
2 | Feb-13 | 1 |
Hi @PetePatel,
Try this:
data have;
input ID Month :monyy. _1p_flag;
format Month monyy.;
cards;
1 Mar-12 0
1 Apr-12 0
1 May-12 1
1 Jun-12 1
1 Jul-12 0
1 Aug-12 0
1 Sep-12 1
1 Oct-12 0
2 Apr-12 1
2 May-12 0
2 Jun-12 0
2 Jul-12 1
2 Aug-12 0
2 Sep-12 0
2 Oct-12 0
2 Nov-12 1
2 Dec-12 0
2 Jan-13 1
2 Feb-13 1
;
data want(drop=f0 m1);
set have;
by id month;
retain f0 m1;
if first.id then call missing(f0,m1);
if _1p_flag then do;
if f0 then do;
_1_to_0_to_1=1;
months_between=intck('month',m1,month);
end;
m1=month;
f0=.;
end;
else if m1>. then f0=1;
run;
Hi @PetePatel,
Try this:
data have;
input ID Month :monyy. _1p_flag;
format Month monyy.;
cards;
1 Mar-12 0
1 Apr-12 0
1 May-12 1
1 Jun-12 1
1 Jul-12 0
1 Aug-12 0
1 Sep-12 1
1 Oct-12 0
2 Apr-12 1
2 May-12 0
2 Jun-12 0
2 Jul-12 1
2 Aug-12 0
2 Sep-12 0
2 Oct-12 0
2 Nov-12 1
2 Dec-12 0
2 Jan-13 1
2 Feb-13 1
;
data want(drop=f0 m1);
set have;
by id month;
retain f0 m1;
if first.id then call missing(f0,m1);
if _1p_flag then do;
if f0 then do;
_1_to_0_to_1=1;
months_between=intck('month',m1,month);
end;
m1=month;
f0=.;
end;
else if m1>. then f0=1;
run;
data have;
input ID Month :monyy. _1p_flag;
format Month monyy.;
cards;
1 Mar-12 0
1 Apr-12 0
1 May-12 1
1 Jun-12 1
1 Jul-12 0
1 Aug-12 0
1 Sep-12 1
1 Oct-12 0
2 Apr-12 1
2 May-12 0
2 Jun-12 0
2 Jul-12 1
2 Aug-12 0
2 Sep-12 0
2 Oct-12 0
2 Nov-12 1
2 Dec-12 0
2 Jan-13 1
2 Feb-13 1
;
data want;
retain has_one n 0;
do i=1 by 1 until(last._1p_flag);
set have;
by id _1p_flag notsorted;
if first.id then do;has_one=0;n=0;end;
if first._1p_flag and _1p_flag=1 and has_one=1 then do; _1_to_0_to_1=1;months_between=n+1;end;
output;
call missing(_1_to_0_to_1,months_between);
end;
n=i;
if _1p_flag=1 then has_one=1;
drop n i has_one;
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.