topic Re: Sequence in SAS Programming

Sequence

Jarious — Fri, 10 Jun 2016 18:23:02 GMT

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

Re: Sequence

Pamela_JSRCC — Fri, 10 Jun 2016 19:11:21 GMT

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

Re: Sequence

Ksharp — Sat, 11 Jun 2016 07:31:51 GMT

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;

Re: Sequence

Astounding — Sat, 11 Jun 2016 15:07:15 GMT

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.