data managing

[sijansap]

Got a question.

Data have;

Input X1-X10;

cards;

7.2    8.1    8.2    9.1

1.1   1.4     5.2     2.3    7.1    8.1    1.5

8.1    8.1    8.3    8.2    8.1

2.1    2.2    2.5    7.3.

7.1    7.2    8.1    8.0    8.3    8.1    9.1

..........; (thousands of rows)

I want to reorder the rows in descending order according to the number after the decimal. Front number doesn't matter as long as numbers after the decimal are  are in descending order. But, the numbers before and after the decimal should be together. In other words,

Data want;

7.2    8.2    8.1    9.1

1.5    1.4    2.3    5.2     7.1    8.1    1.1

8.3    8.2    8.1    8.1    8.1

2.5    7.3    2.2    2.1

8.3    7.2    7.1     8.1    9.1   8.1   8.0

..................,

I thank you very much if any one of the expert out there could help me to get the data I wanted.

[ketpt42 · Accepted solution]

Data have;

infile cards missover;
Input X1-X10 ;
cards;
7.2    8.1    8.2    9.1
1.1   1.4     5.2     2.3    7.1    8.1    1.5
8.1    8.1    8.3    8.2    8.1
2.1    2.2    2.5    7.3
7.1    7.2    8.1    8.0    8.3    8.1    9.1
;
run;

data want;
    set have;
    array x(10);
    array rev(10) _temporary_;
    do i = 1 to dim(x);
        rev(i)=input(reverse(put(x(i),3.1)), 3.1);
    end;
    
    call sortn(of rev[*]);
    
    do i = 1 to dim(x);
        x(i) = input(reverse(put(rev(dim(x) + 1 - i),3.1)), 3.1);
    end;
    drop i;
run;

[sijansap]

Thank you so much, that is exactly what I wanted.
Thanks again for your help.

Sijan Sap

[Reeza]

Data have;
infile cards truncover;
Input X1-X10;
ID = _n_;
cards;
7.2    8.1    8.2    9.1
1.1   1.4     5.2     2.3    7.1    8.1    1.5
8.1    8.1    8.3    8.2    8.1
2.1    2.2    2.5    7.3.
7.1    7.2    8.1    8.0    8.3    8.1    9.1
;;;;
run;

*flip to wide;
proc transpose data=have out=long;
by ID;
var x1-x10;
run;

*separate out decimal and integer portions;
data separate;
set long;

int = int(col1);
dec = col1- int;

run;

*sort as needed;
proc sort data=separate;
by id descending dec;
run;

*flip back to a wide format;
proc transpose data=separate out=want prefix=X ;
by id;
var col1;
run;

Definitely a longer approach but this is one option as well.

---

@sijansap wrote:

Got a question.

Data have;

Input X1-X10;

cards;

7.2    8.1    8.2    9.1

1.1   1.4     5.2     2.3    7.1    8.1    1.5

8.1    8.1    8.3    8.2    8.1

2.1    2.2    2.5    7.3.

7.1    7.2    8.1    8.0    8.3    8.1    9.1

..........; (thousands of rows)

I want to reorder the rows in descending order according to the number after the decimal. Front number doesn't matter as long as numbers after the decimal are  are in descending order. But, the numbers before and after the decimal should be together. In other words,

Data want;

7.2    8.2    8.1    9.1

1.5    1.4    2.3    5.2     7.1    8.1    1.1

8.3    8.2    8.1    8.1    8.1

2.5    7.3    2.2    2.1

8.3    7.2    7.1     8.1    9.1   8.1   8.0

..................,

I thank you very much if any one of the expert out there could help me to get the data I wanted.

---

[sijansap]

Thanks Reeza,
Sure this is an option. I have 45 columns and several thousand rows, I am afraid, It might get overwhelmed with double transpose. Anyway, thanks for your help.
Sijan Sap

[Reeza]

45 * 10,000 is 450,000 which SAS wouldn't have issues with at all, even running on a Microsoft Surface. Since SAS does the majority of its data management not in memory that's less of an issue with SAS as compared to other languages. This approach is actually a bit more dynamic than the data step but either will work for you.

[Ksharp]

data have;
infile cards truncover;
Input X1-X10 ;
cards;
7.2    8.1    8.2    9.1
1.1   1.4     5.2     2.3    7.1    8.1    1.5
8.1    8.1    8.3    8.2    8.1
2.1    2.2    2.5    7.3
7.1    7.2    8.1    8.0    8.3    8.1    9.1
;
run;
data want;
 set have;
 array x{*} x1-x10;
 array y{*} y1-y10;
 do i=1 to dim(x);
   if not missing(x{i}) then y{i}=mod(x{i},1);
 end;
 do m=1 to i-2;
   do n=m+1 to i-1;
     if y{m}<y{n} then do;
       temp=x{m};x{m}=x{n};x{n}=temp;
	   temp=y{m};y{m}=y{n};y{n}=temp;
     end;
   end;
 end;
drop temp y1-y10 i m n;
run;