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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: