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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.