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