BookmarkSubscribeRSS Feed
subrat1
Fluorite | Level 6

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
7 REPLIES 7
kiranv_
Rhodochrosite | Level 12

try this

proc sql;
select old from want
order by scan(old, -1,'_');
novinosrin
Tourmaline | Level 20
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;
Shmuel
Garnet | Level 18

 

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;

      

Astounding
PROC Star

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;

novinosrin
Tourmaline | Level 20

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

Astounding
PROC Star

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.

novinosrin
Tourmaline | Level 20

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 927 views
  • 2 likes
  • 5 in conversation