HOHO. you must be kidding with me .
data cust; input id :$2. cust:$3. date:date9.; format date date9.; cards; a1 c11 20May2013 a1 c11 15Jul2013 a1 c12 08Jan2014 a1 c11 25Apr2014 a1 c11 25May2014 a1 c11 25Jun2014 a1 c11 25Jul2014 a2 c21 20Jun2013 a2 c23 15Jul2013 a2 c21 08Aug2013 a2 c24 25Apr2014 a3 c31 20Jun2013 a3 c32 15Jul2013 a3 c31 08Aug2013 a3 c32 25Feb2014 a3 c31 30Jul2014 ;;;; run; %macro extract(start=may2013,end=apr2014); %let s=01&start ; %let e=%sysfunc(intnx(month,"01&end."d ,0,e),date9.); data temp(keep=id changed vname); set cust(where=(date between "&s"d and "&e"d)); by id notsorted ; length vname $ 40; retain vname "&start._&end"; if cust ne lag(cust) then changed+1; if first.id then changed=0; if last.id; run; proc append base=x data=temp force;run; %mend extract; options mprint symbolgen; %extract(start=may2013,end=apr2014) %extract(start=Jun2013,end=May2014) %extract(start=Jul2013,end=Jun2014) %extract(start=Aug2013,end=jul2014) proc sort data=x;by id;run; proc transpose data=x out=want(drop=_:) ; by id; id vname; var changed; run;
Xia Keshan
data have;
input acct_nbr $ custid $ date : date9.;
format date date9.;
_custid=input(compress(custid,,'kd'),best.);
mon=month(date);
month=date;
format month monyy7.;
cards;
a1 c11 20Jun2013
a1 c11 15Jul2013
a1 c12 08Aug2013
a1 c11 25Aug2013
a2 c21 20Jun2013
a2 c23 15Jul2013
a2 c21 08Aug2013
a2 c24 25Aug2013
a3 c31 20Jun2013
a3 c32 15Jul2013
a3 c31 08Aug2013
a3 c32 25Aug2013
a3 c31 30Aug2013
;;;;
run;
data want (keep=acct_nbr month shift);
set have;
by acct_nbr mon _custid notsorted;
if first.acct_nbr and first.mon then shift=0;
if not first.mon and _custid-lag(_custid) ne 0 then shift+1;
if last.mon then output;
run;
proc tabulate data=want;
class acct_nbr month;
var shift;
table acct_nbr=' ',month=' '*shift=' '*sum=' '/box='acct_nbr';
run;
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.