topic Arrange column as per date suffix from old to new in SAS Programming

Arrange column as per date suffix from old to new

subrat1 — Mon, 29 Jan 2018 05:17:50 GMT

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

Re: Arrange column as per date suffix from old to new

kiranv_ — Mon, 29 Jan 2018 05:58:00 GMT

try this

proc sql;
select old from want
order by scan(old, -1,'_');

Re: Arrange column as per date suffix from old to new

novinosrin — Mon, 29 Jan 2018 06:14:33 GMT

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;

Re: Arrange column as per date suffix from old to new

Shmuel — Mon, 29 Jan 2018 09:04:13 GMT

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;

Re: Arrange column as per date suffix from old to new

Astounding — Mon, 29 Jan 2018 10:20:24 GMT

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;

Re: Arrange column as per date suffix from old to new

novinosrin — Mon, 29 Jan 2018 17:25:18 GMT

@Astounding Sir, I'm afraid your code isn't producing the OP's desired result. The sort is not so straight forward.

Re: Arrange column as per date suffix from old to new

Astounding — Mon, 29 Jan 2018 17:43:46 GMT

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.

Re: Arrange column as per date suffix from old to new

novinosrin — Mon, 29 Jan 2018 17:57:04 GMT

Yes, that;s the reason i captured the year and reordered by mmddyy8. values earlier. I assumed you may have seen my code.