Sequence

[Jarious]

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 

[Pamela_JSRCC]

/* 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;

[Ksharp]

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;

[Astounding]

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.