track based on groups

Reply
Contributor
Posts: 44

track based on groups

I have the below :

LP_STATUS_IDUPDT_DTCT_KEY
VI29-Dec-141
NOLP21-Jan-151
CR4-Nov-142
NOLP14-Dec-142
LP4-Jan-144
NOLP5-Jul-134
LP21-Jul-145
CR4-Feb-145
NOLP4-Mar-145
VI4-Jun-146
NOLP4-Nov-146
CR4-Oct-147
ST4-Sep-147
NOLP4-Aug-147
VI4-Jul-147

I  am trying to get the output table as below

CT_KEYChanged
  fromVi-NoLP
Changed from CR-NOLP
11
21

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

Respected Advisor
Posts: 4,804

Re: track based on groups

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=_Smiley Happy;

by ct_key;

var dummy;

id status_change_var;

idlabel status_change_label;

run;

PG

PG
New Contributor
Posts: 3

Re: track based on groups

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;

Super User
Posts: 9,856

Re: track based on groups

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

New Contributor
Posts: 3

Re: track based on groups

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

;

Super User
Posts: 9,856

Re: track based on groups

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

Ask a Question
Discussion stats
  • 5 replies
  • 254 views
  • 1 like
  • 4 in conversation