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 ..
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
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;
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
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
;
That is the reason why I am not able to catch on OP .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.