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