topic Re: Rearrange strings to always have the lowest in column1 in SAS Enterprise Guide

Rearrange strings to always have the lowest in column1

Sean_OConnor — Wed, 10 Oct 2018 14:37:53 GMT

Folks,

I have the following dataset;

data have;
infile cards expandtabs;
input Var1 $  Var2 $ ;
cards;
0100	104428	 
2566	36186	
3	507094	
40156	2566	
588	49	
	

;
run;

I need to rearrange the strings so the lowest value is always in column1. We need to end up with something like this;

data want;
infile cards expandtabs;
input Var1 $  Var2 $ ;
cards;
0100	104428	 
2566	36186	
3	507094	
2566 40156	
49	588
	

;
run;

Any insight welcome

Re: Rearrange strings to always have the lowest in column1

novinosrin — Wed, 10 Oct 2018 14:43:08 GMT

data have;
infile cards expandtabs;
input Var1 $  Var2 $ ;
cards;
0100	104428	 
2566	36186	
3	507094	
40156	2566	
588	49	
	

;
run;

data want;
set have;
call sortc(var1, var2);
run;

Re: Rearrange strings to always have the lowest in column1

FreelanceReinh — Wed, 10 Oct 2018 14:55:58 GMT

Please note that for strings, e.g., '104428' < '3'.

Re: Rearrange strings to always have the lowest in column1

Sean_OConnor — Wed, 10 Oct 2018 14:58:14 GMT

While not in the example above the strings I'm working on will all be length 13 and alphanumeric. They always end with a letter.

Re: Rearrange strings to always have the lowest in column1

PeterClemmensen — Wed, 10 Oct 2018 15:15:05 GMT

It is always a good idea to post example data that resembles your actual data.

Now, when they always end with a letter, how do you determine what value is larger?

Re: Rearrange strings to always have the lowest in column1

singhsahab — Wed, 10 Oct 2018 15:15:30 GMT

data have;
infile cards expandtabs;
input Var1 $ Var2 $ ;
cards;
0100 104428
2566 36186
3 507094
40156 2566
588 49
;
run;

data want;
set have;
array arr1[*] _all_;
array arr2[*] _ALL_;
do i=1 to dim(arr1);
do j=1 to dim(arr2);
if arr1[i]>arr2[j] then var1=arr2[j];
if arr1[i]<arr2[j] then var2=arr2[j];
end;
end;
drop i j;
run;

Re: Rearrange strings to always have the lowest in column1

ballardw — Wed, 10 Oct 2018 19:00:07 GMT

@PeterClemmensen wrote:

It is always a good idea to post example data that resembles your actual data.

Now, when they always end with a letter, how do you determine what value is larger?

For an example is 123N supposed to come before or after 123a?

Default character comparisons will have all the upper case come before any of the lower case. This may not be an issue if your letters are always all upper or all lower case. If they have mixed case then your order potentially may not be as desired.