Desktop productivity for business analysts and programmers

Sort columns independently

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Sort columns independently

Goodevening everyone Smiley Happy

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!


Accepted Solutions
Solution
‎04-04-2018 03:48 PM
Esteemed Advisor
Posts: 5,617

Re: Sort columns independently

Sort by MetricName MetricValue.

PG

View solution in original post


All Replies
PROC Star
Posts: 1,333

Re: Sort columns independently

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

Contributor
Posts: 20

Sort columns independently

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.
Super User
Posts: 23,963

Re: Sort columns independently

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.

Contributor
Posts: 20

Re: Sort columns independently

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 Smiley Happy !!

Solution
‎04-04-2018 03:48 PM
Esteemed Advisor
Posts: 5,617

Re: Sort columns independently

Sort by MetricName MetricValue.

PG
Contributor
Posts: 20

Re: Sort columns independently

Thanks a lot!

Super User
Posts: 2,049

Re: Sort columns independently

[ Edited ]
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
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 309 views
  • 1 like
  • 5 in conversation