01-29-2018 12:17 AM
I have one column "old" , I want to create a new column "new" where columns value ar arranged as per date suffix from old to new as shown below.
01-29-2018 01:14 AM
data have; input old : $40.; _k1=compress(old,' ','d'); _k2 =input(compress(old,' ','kd'),mmddyy8.); datalines; Bench_010218 Bench_101917 Bench_010818 Bench_102317 Bench_011518 Bench_103017 Bench_012218 Bench_111317 Bench_101917 Bench_112017 Bench_102317 Bench_112717 Bench_103017 Bench_120517 Bench_111317 Bench_121117 Bench_112017 Bench_121817 Bench_112717 Bench_122617 Bench_120517 Bench_010218 Bench_121117 Bench_010818 Bench_121817 Bench_011518 Bench_122617 Bench_012218 Bench_Variance Bench_Variance Cons_div Cons_div Cons_lob Cons_lob Cons_program_type Cons_program_type Count_010218 Count_101917 Count_010818 Count_102317 Count_011518 Count_103017 Count_012218 Count_111317 Count_101917 Count_112017 Count_102317 Count_112717 Count_103017 Count_120517 Count_111317 Count_121117 Count_112017 Count_121817 Count_112717 Count_122617 Count_120517 Count_010218 Count_121117 Count_010818 Count_121817 Count_011518 Count_122617 Count_012218 Count_Variance Count_Variance ; proc sort data=have out=_have(keep=old); by _k1 _k2; run; data want; set have; set _have(rename=(old=new)); keep old new; run;
01-29-2018 04:04 AM
Try next code:
data temp1; set have; retain myorder; myorder + 1; order_by = scan(old,2,'_'); run; proc sort data=temp1(keep=old order_by) out=temp2(rename=(old=new)); by order_by; run; data temp3(keep=myorder new); set temp2; retain myorder; myorder + 1; run; data want(keep=old new); merge temp1 temp3; by myorder; run;
01-29-2018 05:20 AM
This is probably too simple for what you want:
proc sort data=have;
If you need both OLD and NEW on the same observation, you could use:
proc sort data=have (keep=old) out=new;
merge have new (rename=(old=new));
01-29-2018 12:43 PM
You're right ... I looked at the beginning of the series and not at the end. There's a year built into the end of the name that has to be captured and processed. It can be done, but takes a little more work.
Also note, sorting produces different results on an ASCII (numbers less than letters) vs. EBCDIC (numbers greater than letters) system.