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
/* 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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.