Sorting column order

Discussion:

(too old to reply)

k***@gmail.com

2006-09-12 18:12:50 UTC

I have an issue that has been stumping me.

Does anyone know of an automated way to sort column orders for output?
For example...

I have fields with names:
AAA BBB DDD CCC

That's the variable order in my SAS dataset. I want the columns to be
sorted alphabetically though. (I have about 800 columns that can change
names, so manual hardcording isn't possible.)

Does this question make sense?

Thanks for any help!

illusion

2006-09-12 18:53:04 UTC

Permalink

hope this helps....

data sample;
input d c a b g f e;
cards;
1 2 3 4 5 6 7
8 9 10 11 12 13 14
;
run;

proc transpose data = sample out = sample1;
run;

proc sort data = sample1;
by _name_;
run;

proc transpose data = sample1 out = sample2 (drop = _name_);
run;

Post by k***@gmail.com
I have an issue that has been stumping me.
Does anyone know of an automated way to sort column orders for output?
For example...
AAA BBB DDD CCC
That's the variable order in my SAS dataset. I want the columns to be
sorted alphabetically though. (I have about 800 columns that can change
names, so manual hardcording isn't possible.)
Does this question make sense?
Thanks for any help!

Stig Eide

2006-09-12 19:05:18 UTC

Permalink

Post by k***@gmail.com
I have an issue that has been stumping me.
Does anyone know of an automated way to sort column orders for output?

Hi.
This should work. I am not aware of a method that does not need to
rewrite the table.
Stig Eide

data test;
bbbb='sadd';aaaa='fdfd';dddd='dsvxczvx';cccc='zsczc';
run;
data cols (keep=name);
set sashelp.vcolumn (where=(libname='WORK' and memname='TEST'));
run;
proc sort data=cols;
by name;
run;
proc sql noprint;
select name into :names separated by ' '
from cols;
quit;
%let names=&names;
data test;
attrib &names TRANSCODE=YES;
set test;
run;

toby dunn

2006-09-12 18:35:36 UTC

Permalink

Two things come to mind:

SCL which I wont bother writing code for as the SCL Nut will surely show up
and expound upon the greatness of SCL and the how the rest of SAS sucks.
Lets see if he actually writes something usefull and creates the code for
you.

The second thing is simple and will irritate the afore mentioned SCL Nut (
which makes it all the more fun for me ):

Proc SQL NoPrint ;
Select Name Into : Vars Separated By ' , '
From Dictionary.Columns
Where LibName = 'WORK'
And MemName = 'AAA'
Order By Name ;

Create Table Need As
Select &Vars
From Have ;
Quit ;

Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.

From: ***@GMAIL.COM
Reply-To: ***@GMAIL.COM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Sorting column order
Date: Tue, 12 Sep 2006 11:12:50 -0700

I have an issue that has been stumping me.

Does anyone know of an automated way to sort column orders for output?
For example...

I have fields with names:
AAA BBB DDD CCC

That's the variable order in my SAS dataset. I want the columns to be
sorted alphabetically though. (I have about 800 columns that can change
names, so manual hardcording isn't possible.)

Does this question make sense?

Thanks for any help!

Jairaj Sathyanarayana

2006-09-12 18:38:15 UTC

Permalink

You can use something like this.

First, create/receive your dataset as it is.

Extract a list of variables in your dataset & sort the names alphabetically.
proc contents data=yourdata;
ods output variables=vars(keep=variable);
run;
proc sort data=vars;
by variable;
run;

Load the variable names as a comma-separated list into a macro variable.
proc sql;
select distinct variable into :varlist separated by ', ' from vars;
quit;
%put &varlist.;

Now re-create the dataset with the new alphabetically sorted column order.
proc sql;
create table yourdata_redux as
select &varlist. from yourdata;
quit;

I know re-creating kind of looks wasteful, but I can't think of anything
else right now.

Hope this helps.

Jairaj

data _null_;

2006-09-12 19:12:36 UTC

Permalink

To get the desired order where there are "number range names" a1-a10
don't we need to rely on the PROC CONTENTS definition of alphabetical
order. Consider the following.

data work.test;
retain BBB1-BBB20 AAA CCC AAA1-AAA10 BBB 0;
stop;
run;
proc contents noprint out=work.contents(keep=name);
run;

%global alphaNameList;
proc sql noprint;
select name into :alphaNameList SEPARATED ','
from work.contents;

Create Table work.alphaNames As
Select &alphaNameList
From work.test;
quit;

%put NOTE: alphaNameList=&alphaNameList;

proc contents data=work.alphaNames varnum;
run;

Post by toby dunn
SCL which I wont bother writing code for as the SCL Nut will surely show up
and expound upon the greatness of SCL and the how the rest of SAS sucks.
Lets see if he actually writes something usefull and creates the code for
you.
The second thing is simple and will irritate the afore mentioned SCL Nut (
Proc SQL NoPrint ;
Select Name Into : Vars Separated By ' , '
From Dictionary.Columns
Where LibName = 'WORK'
And MemName = 'AAA'
Order By Name ;
Create Table Need As
Select &Vars
From Have ;
Quit ;
Toby Dunn
When everything is coming at you all at once, your in the wrong lane.
A truly happy person is someone who can smile and enjoy the scenery on a
detour.
Subject: Sorting column order
Date: Tue, 12 Sep 2006 11:12:50 -0700
I have an issue that has been stumping me.
Does anyone know of an automated way to sort column orders for output?
For example...
AAA BBB DDD CCC
That's the variable order in my SAS dataset. I want the columns to be
sorted alphabetically though. (I have about 800 columns that can change
names, so manual hardcording isn't possible.)
Does this question make sense?
Thanks for any help!

toby dunn

2006-09-12 19:20:36 UTC

Permalink

Data _Null_ ;

Nope your code and mine get to the same result.

Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.

From: "data _null_;" <***@GMAIL.COM>
Reply-To: "data _null_;" <***@GMAIL.COM>
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: Sorting column order
Date: Tue, 12 Sep 2006 15:12:36 -0400

To get the desired order where there are "number range names" a1-a10
don't we need to rely on the PROC CONTENTS definition of alphabetical
order. Consider the following.

data work.test;
retain BBB1-BBB20 AAA CCC AAA1-AAA10 BBB 0;
stop;
run;
proc contents noprint out=work.contents(keep=name);
run;

%global alphaNameList;
proc sql noprint;
select name into :alphaNameList SEPARATED ','
from work.contents;

Create Table work.alphaNames As
Select &alphaNameList
From work.test;
quit;

%put NOTE: alphaNameList=&alphaNameList;

proc contents data=work.alphaNames varnum;
run;

data _null_;

2006-09-12 19:28:44 UTC

Permalink

No they do not. Take a closer look using my complex names. My point
is PROC CONTENTS knows more about variables names than "we do".

The following code does not give desirable results.

data work.test;
retain BBB1-BBB20 AAA CCC AAA1-AAA10 BBB 0;
stop;
run;
Proc SQL NoPrint ;
Select Name Into : Vars Separated By ','
From Dictionary.Columns
Where LibName = 'WORK'
And MemName = 'TEST'
Order By Name ;

Create Table Need As
Select &Vars
From TEST ;
Quit ;

%put NOTE: VARS=&VARS;
proc contents varnum;
run;

Post by toby dunn
Data _Null_ ;
Nope your code and mine get to the same result.
Toby Dunn
When everything is coming at you all at once, your in the wrong lane.
A truly happy person is someone who can smile and enjoy the scenery on a
detour.
Subject: Re: Sorting column order
Date: Tue, 12 Sep 2006 15:12:36 -0400
To get the desired order where there are "number range names" a1-a10
don't we need to rely on the PROC CONTENTS definition of alphabetical
order. Consider the following.
data work.test;
retain BBB1-BBB20 AAA CCC AAA1-AAA10 BBB 0;
stop;
run;
proc contents noprint out=work.contents(keep=name);
run;
%global alphaNameList;
proc sql noprint;
select name into :alphaNameList SEPARATED ','
from work.contents;
Create Table work.alphaNames As
Select &alphaNameList
From work.test;
quit;
%put NOTE: alphaNameList=&alphaNameList;
proc contents data=work.alphaNames varnum;
run;

toby dunn

2006-09-12 19:29:10 UTC

Permalink

Disregard that last post I missed something. In the other hand if you have
named lists like that you should be beat with in an inch of your life and
then fired.

Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.

From: "data _null_;" <***@GMAIL.COM>
Reply-To: "data _null_;" <***@GMAIL.COM>
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: Sorting column order
Date: Tue, 12 Sep 2006 15:12:36 -0400

To get the desired order where there are "number range names" a1-a10
don't we need to rely on the PROC CONTENTS definition of alphabetical
order. Consider the following.

data work.test;
retain BBB1-BBB20 AAA CCC AAA1-AAA10 BBB 0;
stop;
run;
proc contents noprint out=work.contents(keep=name);
run;

%global alphaNameList;
proc sql noprint;
select name into :alphaNameList SEPARATED ','
from work.contents;

Create Table work.alphaNames As
Select &alphaNameList
From work.test;
quit;

%put NOTE: alphaNameList=&alphaNameList;

proc contents data=work.alphaNames varnum;
run;

data _null_;

2006-09-12 19:47:00 UTC

Permalink

Post by toby dunn
Disregard that last post I missed something. In the other hand if you have
named lists like that you should be beat with in an inch of your life and
then fired.

I don't see what you consider "proper" variable naming has to do with this.

It seems to me that the point is to generate an alphabetical list of
variable names in the 'SAS context" of alphabetical. To do that one
can use PROC CONTENTS because SORTING does not work.

Venky Chakravarthy

2006-09-12 20:14:12 UTC

Permalink

You have heard from several folks. The archive also holds some past answers
to this question.

Let me offer a new approach if you are using the display manager and have
the VIEWTABLE window available. Let us use the SASHELP.CLASS data set for
this demo. This and more are part of my Hands on Workshop on the VIEWTABLE
window.

1. Make a copy of the SASHELP.CLASS metadata structure by reading
it with a SET but using OBS=0 as a dataset option.

data class1 ;
set sashelp.class (obs=0) ;
run ;

2. Now open CLASS1 in a VIEWTABLE window by typing on any command
line or the command box.

VT CLASS1

3. You will receive a NOTE with a red X that the dataset
has 0 observations. Close that to show the current variable order.

4. Under the DATA menu choose Hide/Unhide and move the variables
from the DISPLAYED box en masse to the HIDDEN box by clicking
the left pointing double arrow. You should see them in alphabetical
order now in the HIDDEN box.

5. Move everything back from HIDDEN to DISPLAYED by clicking on the
RIGHT pointing double arrow. The variables will now be in
alphabetical order in the DISPLAYED box. Click ok.

6. Choose SAVE AS from the FILE menu and choose the WORK library
to save as CLASS2.

7. Now use the metadata structure in CLASS2 to reorder your columns in
sashelp.class

data toview / view = toview ;
set class2 sashelp.class ;
run ;

proc print data = toview ;
run ;

Result:

Obs AGE HEIGHT NAME SEX WEIGHT

1 14 69 Alfred M 112.5
2 13 56.5 Alice F 84
3 13 65.3 Barbara F 98
4 14 62.8 Carol F 102.5
5 14 63.5 Henry M 102.5
6 12 57.3 James M 83
7 12 59.8 Jane F 84.5
8 15 62.5 Janet F 112.5
9 13 62.5 Jeffrey M 84
10 12 59 John M 99.5
11 11 51.3 Joyce F 50.5
12 14 64.3 Judy F 90
13 12 56.3 Louise F 77
14 15 66.5 Mary F 112
15 16 72 Philip M 150
16 12 64.8 Robert M 128
17 15 67 Ronald M 133
18 11 57.5 Thomas M 85
19 15 66.5 William M 112

Venky Chakravarthy

Richard A. DeVenezia

2006-09-13 01:59:50 UTC

Permalink

Post by k***@gmail.com
I have an issue that has been stumping me.
Does anyone know of an automated way to sort column orders for output?
For example...
AAA BBB DDD CCC
That's the variable order in my SAS dataset. I want the columns to be
sorted alphabetically though. (I have about 800 columns that can
change names, so manual hardcording isn't possible.)
Does this question make sense?

800 columns is oftne hard to make sense of.

This code sample will rebuild a table such that the column names are sorted
by prefix and numeric suffix.
--------------------------------------
%macro ugly_factory (n=300, out=ugly);

option NOQUOTELENMAX ;

data prefixes;
words = "
The Unanimous Declaration of the Thirteen United States of
America

When, in the course of human events, it becomes necessary for one
people to dissolve the political bonds which have connected them
with another, and to assume among the powers of the earth, the
separate and equal station to which the laws of nature and of
nature's God entitle them, a decent respect to the opinions of
mankind requires that they should declare the causes which impel
them to the separation.
";

i = 1;

do while (scan(words,i) ne '');
prefix = compress(scan(words,i,collate(0,64)));
output;
i+1;
end;

keep prefix;
run;

data _null_;

retain seed 654321;

length vars $32000;
do _n_ = 1 to &n;
p = 1 + floor (nobs*ranuni(seed));
set prefixes point=p nobs=nobs;
vars = catx(',',vars,cats(prefix,ceil(250*ranuni(seed))));
end;

call symput('vars', trim(vars));
stop;
run;

data ugly;
call missing (&vars, fee, fi, fo, phum, x);
run;

%mend;

%ugly_factory()

proc sql noprint;
create view name_split as
select name
,
indexc(left(reverse(name)),compress(collate(0,255),collate(30x,39x))) as p
, case calculated p
when 1
then upcase(name)
else upcase(substr(name,1,length(name)-(calculated p)+1))
end as left
, case calculated p
when 1
then 0
else input(substr(name,length(name)-(calculated p)+2),best12.)
end as right
from dictionary.columns
where libname='WORK' and memname='UGLY'
;

select name
into :names separated by ' '
from name_split
order by
left, right
;
quit;

data ugly;
retain &names;
set ugly;
run;
--------------------------------------

Richard A. DeVenezia
http://www.devenezia.com/

David L Cassell

2006-09-13 05:51:41 UTC

Permalink

Okay, so the variable order may be complicated under certain scenarios.
I still like Toby's solution.

Plus, if the Original Poster only wants to *display* the output and not
create a new data set, then Toby's solution can be used like this:

Proc SQL NoPrint;
Select Name Into : Vars Separated By ' , '
From Dictionary.Columns
Where LibName = 'WORK' and MemName = 'AAA'
Order By Name;
Quit;

proc print noobs data=aaa;
var &VARS. ;
run;

HTH,
David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330

_________________________________________________________________
Call friends with PC-to-PC calling -- FREE
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmailtagline

data _null_;

2006-09-13 09:42:51 UTC

Permalink

Post by David L Cassell
Okay, so the variable order may be complicated under certain scenarios.
I still like Toby's solution.

What does like have to do with it. If we want it to be right we need
to rely on PROC CONTENTS to order this list for use or use the method
posted by Richard.

Post by David L Cassell
Plus, if the Original Poster only wants to *display* the output and not

Any of the "solutions" can be used in that way.

David L Cassell

2006-09-13 19:02:23 UTC

Permalink

Post by data _null_;

Post by David L Cassell
Okay, so the variable order may be complicated under certain scenarios.
I still like Toby's solution.

What does like have to do with it. If we want it to be right we need
to rely on PROC CONTENTS to order this list for use or use the method
posted by Richard.

Yes.

But the approach taken by Toby does have valuable structure. It
does not give the right sort order under all conditions, but people can
still learn from the code.

Post by data _null_;

Post by David L Cassell
Plus, if the Original Poster only wants to *display* the output and not

Any of the "solutions" can be used in that way.

Agreed, also. I didn't make that point clear at all, did I?

David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330

_________________________________________________________________
Call friends with PC-to-PC calling -- FREE
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmailtagline

Continue reading on narkive:

Search results for 'Sorting column order' (Questions and Answers)

replies

About sorting in Excel?

started 2011-03-29 01:27:55 UTC

software

replies

The first line of my Excel spreadsheet is the "heading" for the columns. How do I keep it from being sorted?

started 2009-02-09 13:49:59 UTC

software

replies

Excel question - sorting columns?