topic Re: data managing in SAS Programming

data managing

sijansap — Tue, 07 Jul 2020 21:17:05 GMT

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.

Re: data managing

ketpt42 — Tue, 07 Jul 2020 22:00:31 GMT

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;

Re: data managing

Reeza — Tue, 07 Jul 2020 22:15:27 GMT

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.

Re: data managing

sijansap — Tue, 07 Jul 2020 22:36:25 GMT

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

Re: data managing

sijansap — Tue, 07 Jul 2020 22:45:08 GMT

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

Sijan Sap

Re: data managing

Reeza — Wed, 08 Jul 2020 01:20:40 GMT

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.

Re: data managing

Ksharp — Wed, 08 Jul 2020 13:31:05 GMT

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;