track based on groups

[boin]

I have the below :

LP_STATUS_ID	UPDT_DT	CT_KEY
VI	29-Dec-14	1
NOLP	21-Jan-15	1
CR	4-Nov-14	2
NOLP	14-Dec-14	2
LP	4-Jan-14	4
NOLP	5-Jul-13	4
LP	21-Jul-14	5
CR	4-Feb-14	5
NOLP	4-Mar-14	5
VI	4-Jun-14	6
NOLP	4-Nov-14	6
CR	4-Oct-14	7
ST	4-Sep-14	7
NOLP	4-Aug-14	7
VI	4-Jul-14	7

I  am trying to get the output table as below

CT_KEY	Changed   fromVi-NoLP	Changed from CR-NOLP
1	1
2		1

Tried several ways  like first and last based on dates but no luck, any help would be great ..

[PGStats]

If I understood what you want :

data have;

input LP_STATUS_ID $ UPDT_DT :anydtdte. CT_KEY;

datalines;

VI 29-Dec-14 1

NOLP 21-Jan-15 1

CR 4-Nov-14 2

NOLP 14-Dec-14 2

LP 4-Jan-14 4

NOLP 5-Jul-13 4

LP 21-Jul-14 5

CR 4-Feb-14 5

NOLP 4-Mar-14 5

VI 4-Jun-14 6

NOLP 4-Nov-14 6

CR 4-Oct-14 7

ST 4-Sep-14 7

NOLP 4-Aug-14 7

VI 4-Jul-14 7

;

data temp;

length from_status $8 status_change_var $16 status_change_label $20 dummy $1;

dummy = "1";

do until (last.ct_key);

    set have; by ct_key;

    status_change_var = catx("_", from_status, LP_status_id);

    status_change_label = catx(" ", "Changed from", catx("-", from_status, LP_status_id));

    if not first.ct_key then output;

    from_status = lp_status_id;

    end;

keep ct_key status_change_var status_change_label dummy;

run;

proc transpose data=temp out=want(drop=_:);

by ct_key;

var dummy;

id status_change_var;

idlabel status_change_label;

run;

PG

[szhao3]

In my understanding  boin might want to count freq of the same route.

thanks for PGStats. just got how to input dd-mmm-yy

data have;

input LP_STATUS_ID $ UPDT_DT :anydtdte. CT_KEY;

datalines;

VI 29-Dec-14 1

NOLP 21-Jan-15 1

CR 4-Nov-14 2

NOLP 14-Dec-14 2

LP 4-Jan-14 4

NOLP 5-Jul-13 4

LP 21-Jul-14 5

CR 4-Feb-14 5

NOLP 4-Mar-14 5

VI 4-Jun-14 6

NOLP 4-Nov-14 6

CR 4-Oct-14 7

ST 4-Sep-14 7

NOLP 4-Aug-14 7

VI 4-Jul-14 7

;

proc sort data=have out=have1;

    by ct_key updt_dt lp_status_id;

run;

data have1;

    set have1;

    by ct_key updt_dt lp_status_id;

    retain _temp;

    if first.ct_key then _temp=lp_status_id;

    if not first.ct_key and _temp ne lp_status_id then do;

        _lp=catx('_to_',_temp,lp_status_id);

        _temp=lp_status_id;

    end;

run;

proc freq data=have1(where=(not missing(_lp))) noprint;

   tables ct_key*_lp / out=have2;

run;

proc transpose data=have2 out=want(drop=_name_ _label_) prefix=Changed_from_;

    id _lp;

    var count;

    by ct_key;

run;

[Ksharp]

Still hard to catch you .

data have;
input LP_STATUS_ID $ UPDT_DT :anydtdte. CT_KEY;
datalines;
VI 29-Dec-14 1
NOLP 21-Jan-15 1
CR 4-Nov-14 2
NOLP 14-Dec-14 2
LP 4-Jan-14 4
NOLP 5-Jul-13 4
LP 21-Jul-14 5
CR 4-Feb-14 5
NOLP 4-Mar-14 5
VI 4-Jun-14 6
NOLP 4-Nov-14 6
CR 4-Oct-14 7
ST 4-Sep-14 7
NOLP 4-Aug-14 7
VI 4-Jul-14 7
;
data temp;
 set have;
 by  CT_KEY ;
 length text $ 100;
 retain text ;
 retain var 1;
 text=catx('_',text,LP_STATUS_ID);
 if last.CT_KEY then do;output;call missing(text);end;
 keep  CT_KEY var text;
run;
proc transpose data=temp out=want(drop=_:) ;
by CT_KEY;
var var;
id text;
run;

Xia Keshan

[szhao3]

For example, for CT_KEY=7, you summarized change CR_ST_NOLP_VI and I counted each individual change CR_ST ST_NOLP NOLP_VI.

If there were repeated change, its frequency would gt 1 as the following modified data shows.  Anyway i am not sure what boin asked for.

data have;

input LP_STATUS_ID $ UPDT_DT :anydtdte. CT_KEY;

datalines;

VI 29-Dec-14 1

NOLP 21-Jan-15 1

CR 4-Nov-14 2

NOLP 14-Dec-14 2

LP 4-Jan-14 4

NOLP 5-Jul-13 4

LP 21-Jul-14 5

CR 4-Feb-14 5

NOLP 4-Mar-14 5

VI 4-Jun-14 6

NOLP 4-Nov-14 6

CR 4-Oct-14 7

ST 4-Sep-14 7

CR 6-Oct-14 7

ST 5-Oct-14 7

NOLP 4-Aug-14 7

VI 4-Jul-14 7

;

[Ksharp]

That is the reason why I am not able to catch on OP .