DATA Step, Macro, Functions and more

Arrange column as per date suffix from old to new

Reply
Contributor
Posts: 58

Arrange column as per date suffix from old to new

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.

OldNew
Bench_010218Bench_101917
Bench_010818Bench_102317
Bench_011518Bench_103017
Bench_012218Bench_111317
Bench_101917Bench_112017
Bench_102317Bench_112717
Bench_103017Bench_120517
Bench_111317Bench_121117
Bench_112017Bench_121817
Bench_112717Bench_122617
Bench_120517Bench_010218
Bench_121117Bench_010818
Bench_121817Bench_011518
Bench_122617Bench_012218
Bench_VarianceBench_Variance
Cons_divCons_div
Cons_lobCons_lob
Cons_program_typeCons_program_type
Count_010218Count_101917
Count_010818Count_102317
Count_011518Count_103017
Count_012218Count_111317
Count_101917Count_112017
Count_102317Count_112717
Count_103017Count_120517
Count_111317Count_121117
Count_112017Count_121817
Count_112717Count_122617
Count_120517Count_010218
Count_121117Count_010818
Count_121817Count_011518
Count_122617Count_012218
Count_VarianceCount_Variance
PROC Star
Posts: 549

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

[ Edited ]

try this

proc sql;
select old from want
order by scan(old, -1,'_');
Super User
Posts: 2,066

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

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;
Trusted Advisor
Posts: 1,848

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

 

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;

      

Super User
Posts: 6,935

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

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;

Super User
Posts: 2,066

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

Posted in reply to Astounding

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

Super User
Posts: 6,935

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

Posted in reply to novinosrin

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.

Super User
Posts: 2,066

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

Posted in reply to Astounding

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

Ask a Question
Discussion stats
  • 7 replies
  • 191 views
  • 2 likes
  • 5 in conversation