BookmarkSubscribeRSS Feed
boin
Obsidian | Level 7

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 ..

5 REPLIES 5
PGStats
Opal | Level 21

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

PG
szhao3
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 588 views
  • 1 like
  • 4 in conversation