DATA Step, Macro, Functions and more

Sorting SAS Table with variable name as output

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Sorting SAS Table with variable name as output

[ Edited ]

I have the follwing table 

IDa1s2d3f4g5
a 102.562173.367833.538423.00214
s 22.5621702.874134.13333.60389
d 33.367832.8741302.467643.39893
f 43.538424.13332.4676402.97952
g 53.002143.603893.398932.979520

 

I want to sort this table rowwise(or may be columnwise) but instead of the observation value I need the variable name as output, something like below

 

IDid1id2id3id4id5
a 1a1s2g5d3f4
s 2s2a1d3g5f4
d 3d3f4s2a1g5
f 4f4d3g5a1s2
g 5g5f4a1d3s2

 

Some more details:

Considering only the first row of the table

IDa1s2d3f4g5
a 102.562173.367833.538423.00214

 

Sorting It alonwith variable names

IDa1s2d3f4g5
a 102.562173.367833.538423.00214

 

Resulting into

IDa1s2g5d3f4
a 102.562173.002143.367833.53842

Now taking only the variable names

ID     
a 1a1s2g5d3f4

Doing it for all the rows is my desired output

 

 


Accepted Solutions
Solution
‎07-12-2016 01:09 AM
Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3.00214	3.60389	3.39893	2.97952	0
;
run;


proc transpose data=have out=temp;
by id notsorted;
run;
proc sort data=temp;
by id col1;
run;
proc transpose data=temp out=want(drop=_:) prefix=id;
by id;
var _name_;
run;

View solution in original post


All Replies
PROC Star
Posts: 1,561

Re: Sorting SAS Table with variable name as output

that's one weird puzzle.

why is d3 sometimes followed by f4 and sometimes by g5?

Solution
‎07-12-2016 01:09 AM
Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3.00214	3.60389	3.39893	2.97952	0
;
run;


proc transpose data=have out=temp;
by id notsorted;
run;
proc sort data=temp;
by id col1;
run;
proc transpose data=temp out=want(drop=_:) prefix=id;
by id;
var _name_;
run;
Contributor
Posts: 71

Re: Sorting SAS Table with variable name as output

Wow ! Its done in just few lines and I was thinking about a loop. Thanks a lot !

 

Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

Here is IML code , if you like it . It would be faster than PROC TRANSPOSE.

 

 

data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3.00214	3.60389	3.39893	2.97952	0
;
run;


proc iml;
use have;
read all var _num_ into x[c=vnames r=id];
close;

want=j(nrow(x),ncol(x),blankstr(nleng(vnames)));
do i=1 to nrow(x);
 temp=t(x[i,]);
 call sortndx(ndx,temp,1); 
 want[i,]=t(vnames[ndx]);
end;

names='id1':'id'+char(ncol(x));
create want from want[c=names r=id];
append from want[r=id];
close;
quit;
Contributor
Posts: 71

Re: Sorting SAS Table with variable name as output

Actually I have big data of around 200,0000 rows. This step will be in between the whole logic and the imput would be a square matrix of 200,0000 rows & columns. So what do you suggest in this suggestion. Transpose or IML ? Also could you advice some tutorial/ documentation which can help me in solving such things on my own ...?

 

Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

I would recommend to use IML, if you could make memory bigger by set option  -memsize 20G  in sasconfv9 file.

If you don't have enough memory , try the following code could give you more fast .

 

 

 

data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3.00214	3.60389	3.39893	2.97952	0
;
run;


data want;
 set have;
 array temp{5};
 array have{5} a1	s2	d3	f4	g5;
 array id_{5} $ 32;
 do i=1 to dim(have);
  temp{i}=have{i};
 end;
 call sortn(of temp{*});
 do i=1 to dim(have);
  id_{i}=vname(have{whichn(temp{i},of have{*})});
 end;
 drop i temp: a1	s2	d3	f4	g5;
run;
Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

Hi, If there are duplicated value in an obs, my data step might not work for you .

Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

Actually IML can handle BIG data .

 

 

data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3.00214	3.60389	3.39893	2.97952	0
a11	0	2.56217	3.36783	3.53842	3.00214
s12	2.56217	0	2.87413	4.1333	3.60389
d13	3.36783	2.87413	0	2.46764	3.39893
f14	3.53842	4.1333	2.46764	0	2.97952
g15	3.00214	3.60389	3.39893	2.97952	0
a21	0	2.56217	3.36783	3.53842	3.00214
s22	2.56217	0	2.87413	4.1333	3.60389
d23	3.36783	2.87413	0	2.46764	3.39893
f24	3.53842	4.1333	2.46764	0	2.97952
g25	3.00214	3.60389	3.39893	2.97952	0
;
run;



proc iml;
want=j(1,5,blankstr(32));
id=blankstr(32);
use have;
create want from want[c={ a1 s2 d3 f4 g5} r=id];

setin have;
setout want;

do data;
 read next var _num_ into x[c=vnames r=id];
 call sortndx(ndx,x`,1); 
 want=t(vnames[ndx]);
 
 append from want[r=id];
end;

close have want;
quit;

 

x.png

Contributor
Posts: 71

Re: Sorting SAS Table with variable name as output

Thankyou Xia Keshan. It was a great help. However, it will take good amount of time in testing these codes on actual data, currently I am just trying on Dummy data. I dont know how feasible it will be on actual data, if required I will make clusters to reduce the data. I will inform you which one finally works. I have used proc transpose but IML is new to me. I tried it once and the output was not coming in the form of SAS Table so I did not try the same. Is it possible to save the output from IML in SAS table ? 

 

Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

Yes. That is what I am trying to do. Use the following code to save output into a SAS Table WANT .

 

create want from want;
append from want;
close;
Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

HaHa, I found the following code could handle duplicate value as long as there are no missing value .

 

 

data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3	    3	3.39893	2.97952	0
;
run;


data want;
 set have;
 array have{5} a1	s2	d3	f4	g5;
 array id_{5} $ 32;
 do i=1 to dim(have);
  min=min(of have{*});
  idx=whichn(min,of have{*});
  id_{i}=vname(have{idx});
  have{idx}=.;
 end;
 drop idx min i a1 s2 d3 f4 g5;
run;
Contributor
Posts: 71

Re: Sorting SAS Table with variable name as output

Hi Xia,
Thanks for your support.
I am trying this program on big data but now the problem is while defining array its not possible to write the column names like as you did in the following statement
array have{5} a1 s2 d3 f4 g5;
Now, in first test phase, columns are 100 and that too not in any particular order, could you suggest anything ??
Super User
Posts: 9,681

Re: Sorting SAS Table with variable name as output

Make a macro variable to hold these variables name .
OR you could try my IML code .



data have;
infile cards truncover expandtabs;
input ID $	a1	s2	d3	f4	g5;
cards;
a1	0	2.56217	3.36783	3.53842	3.00214
s2	2.56217	0	2.87413	4.1333	3.60389
d3	3.36783	2.87413	0	2.46764	3.39893
f4	3.53842	4.1333	2.46764	0	2.97952
g5	3	    3	3.39893	2.97952	0
;
run;
proc transpose data=have(obs=0) out=temp;
run;
proc sql noprint;
 select count(*) into : n from temp;
 select _name_ into : list separated by ' ' from temp;
quit;

data want;
 set have;
 array have{&n} &list ;
 array id_{&n} $ 32;
 do i=1 to dim(have);
  min=min(of have{*});
  idx=whichn(min,of have{*});
  id_{i}=vname(have{idx});
  have{idx}=.;
 end;
 keep id id_: ;
run;




PROC Star
Posts: 1,561

Re: Sorting SAS Table with variable name as output

[ Edited ]

The double-dash syntax allows you to list all the variables in a table in the order they are stored.

 

array have{*} a1 -- g5;

 

 


data t; 
  a1=2; b2=3;z5=4; g5=1;
run;
data tt; 
  set t ;
  array have{*} a1 -- g5;
  put have[3]=;
run;

z5=4

 

☑ This topic is SOLVED.

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

Discussion stats
  • 16 replies
  • 429 views
  • 3 likes
  • 3 in conversation