topic Re: Sort columns independently in SAS Enterprise Guide

Sort columns independently

Max05 — Wed, 04 Apr 2018 19:06:01 GMT

Goodevening everyone 🙂

I would like to sort every columns of a dataset in ascending order INDEPENDENTLY.

From;

Weight Height Age

68 175 18

59 189 16

78 169 35

I would like to have

Weight Height Age

59 169 16

68 175 18

78 189 65

I really cant find a solution... Anyone for helping me?

Thanks in advance!

Re: Sort columns independently

TomKari — Wed, 04 Apr 2018 19:15:39 GMT

It's not that difficult, but this looks suspiciously like a homework assignment. Can you give us any insights into what you've tried, and what the business requirement for this could possibly be?

Tom

Sort columns independently

Max05 — Wed, 04 Apr 2018 19:23:49 GMT

It is for my internship actually. I need to sort series of prices of multiple financial instruments composing a specific portfolio, and take the average to be able to calculate multiple risk measures such as VaR, CVaR, SRRI, etc.

Re: Sort columns independently

Reeza — Wed, 04 Apr 2018 19:30:15 GMT

I think this is a case where you should restructure your data then.

Transpose it to a long format so that you have:

MetricName MetricValue

A 2

A 3

A 4

B 2

B 8

B 9

Then use a regular proc sort. Given your next steps this format will work better as well.

Re: Sort columns independently

Max05 — Wed, 04 Apr 2018 19:40:02 GMT

Hi Reeza,

First of all, thanks a lot for your answer. I managed to restructure my data to have that same structure as your example.

Then I tried the following code :

PROC SORT DATA=WORK.Prices
OUT=WORK.test;
BY MetricValue;

RUN;

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_Prices AS
SELECT t1.MetricName,
t1.MetricValue
FROM WORK.Prices t1
ORDER BY t1.MetricValue;
QUIT;

Unfortunately, each time I obtain

A 2

B 2

A 3

A 4

B 8

Instead of

A 2

A 3

A 4

B 2

B 8

B 9

How can I adapt my code then? thank you very much 🙂 !!

Re: Sort columns independently

PGStats — Wed, 04 Apr 2018 19:45:34 GMT

Sort by MetricName MetricValue.

Re: Sort columns independently

Max05 — Wed, 04 Apr 2018 19:48:56 GMT

Thanks a lot!

Re: Sort columns independently

novinosrin — Wed, 04 Apr 2018 19:58:05 GMT

data have;
input Weight                      Height                          Age ;
datalines;
68                                175                             18
59                                189                             16
78                                169                             35
;
data temp;
set have;
array t _numeric_;
do _n_=1 to dim(t);
k=t(_n_);
k1=vname(t(_n_));
output;
end;
run;
proc sort data=temp out=_temp;
by k1 k;
run;

proc transpose data=_temp(keep= k k1) out=want name=k1;
by k1;
var k;
run;

proc transpose data=want out=final_want(drop=_name_);
var col:;
id k1;
run;
proc tra