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.
Old | New |
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 |
try this
proc sql;
select old from want
order by scan(old, -1,'_');
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;
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;
This is probably too simple for what you want:
proc sort data=have;
by old;
run;
If you need both OLD and NEW on the same observation, you could use:
proc sort data=have (keep=old) out=new;
by old;
run;
data want;
merge have new (rename=(old=new));
run;
@Astounding Sir, I'm afraid your code isn't producing the OP's desired result. The sort is not so straight forward.
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.
Yes, that;s the reason i captured the year and reordered by mmddyy8. values earlier. I assumed you may have seen my code.
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.