New Contributor
Posts: 3

# Sequence

My data is attached:

For each ID I need to determine how many times the individual changed from an active client to an inactive client.  I am stumped how to start..any ideas?  This person changed on 3/15, 6/15, 8/15 (not 9/15 or 10/15)  and 1/16

Contributor
Posts: 39

## Re: Sequence

/* create table with all months between the minimum and maximum months in the data */

data yr_mo;
input yr mo;
datalines;
2014 12
2015 01
2015 02
. . .
2015 12
;
run;
data yr_mo;  /* add sequence field */
seq = _n_;
set yr_mo;
run;

proc sql;
create table temp as
select c.id, b.yr, b.mo
from yr_mo A
join yr_mo B on b.seq = a.seq +1 /* month after that in A */
join data C on c.yr = a.yr and        /* active for month in A */
c.mo = a.mo
left join data D on d.id = c.id and
d.yr= b.yr and
d.mo=b.mo
where missing(d.id) /* inactive for month in B */
;
proc sql;
select id, count(*) as row_cnt
from temp
group by id;

Super User
Posts: 10,784

## Re: Sequence

```Assuming I understand what you mean.

data have;
infile cards expandtabs truncover;
input ID	s_year	s_month;
cards;
10000057	2014	12
10000057	2015	1
10000057	2015	2
10000057	2015	4
10000057	2015	5
10000057	2015	7
10000057	2015	11
10000057	2015	12
;
run;

data temp;
merge have have(rename=(ID=_id s_year=_year s_month=_month) firstobs=2);
output;
start=mdy(s_month,1,s_year);
if id=_id then do;
n=intck('month',start,mdy(_month,1,_year))-1;
do i=1 to n;
dummy=1;
temp=intnx('month',start,i);
s_year=year(temp);
s_month=month(temp);
output;
end;
end;
else if _n_ ne 1 then do;
dummy=1;
temp=intnx('month',start,1);
s_year=year(temp);
s_month=month(temp);
output;
end;
drop _: temp n i start;
run;
data want;
set temp;
by id dummy notsorted;
if first.dummy and dummy=1 then changed=1;
run;

```
Super User
Posts: 6,785

## Re: Sequence

[ Edited ]

I would try it this way:

proc sort data=have;

by id year month;

run;

data want;

set have;

by id;

current_date = mdy(month, 1, year);

prior_date = lag(current_date);

if prior_date ne intnx('month', current_date, -1) then changes + 1;

if first.id then changes=0;

if last.id;

run;

This assumes that your goal is as stated, to count the changes (not to identify the year/month).  The count actually omits anything occurring after the final observation (in this case the 1/16 change).  But you could always add 1 at the end to account for that.

Discussion stats
• 3 replies
• 367 views
• 0 likes
• 4 in conversation