DATA Step, Macro, Functions and more

Proc SQL - Ordering Column Names

Reply
Contributor
Posts: 60

Proc SQL - Ordering Column Names

Hello,

 

I have a table that has the below columns...

 

ID   Name1     Name2     Name3     Age1     Age2      Age3     Last1     Last2      Last3

 

With the exception of the column named ID, I would like to re-organise the columns based on number at the end so that I get the below result...

 

ID    Name1    Age1     Last1     Name2     Age2      Last2     Name3     Age3      Last3

 

I have used PROC SQL, to just select them in the order I'd like, however I want to know if there is a quicker way that will do it automatically for me?

 

Cheers

Super User
Super User
Posts: 7,997

Re: Proc SQL - Ordering Column Names

Posted in reply to CamRutherford

There isn't a column order function.  You could do it something like;

data yourds;
id=1; Name1="abc"; Name2="def"; Name3="efg"; Age1=12; Age2=43; Age3=34; Last1="temp"; Last2="temp2"; Last3="Temp3";
run;

proc sql noprint;
select NAME
into :VLIST separated by " "
from SASHELP.VCOLUMN
where LIBNAME="WORK"
and MEMNAME="YOURDS"
and NAME ne "ID"
order by compress(NAME," ","kd"),
case upcase(compress(NAME," ","ka")) when "NAME" then 1 when "LAST" then 2 else 3 end;
quit;

data want;
retain &vlist.;
set yourds;
run;
Super User
Posts: 7,868

Re: Proc SQL - Ordering Column Names

Posted in reply to CamRutherford

A different take, using @RW9's example data, that will automatically adapt itself to any number of variable groups:

data yourds;
 id=1; Name1="abc"; Name2="def"; Name3="efg"; Age1=12; Age2=43; Age3=34; Last1="temp"; Last2="temp2"; Last3="Temp3";
run;

proc sql;
create table names as select name from dictionary.columns where libname = 'WORK' and memname = 'YOURDS';
quit;

data names;
set names;
x = anydigit(name);
if x > 0 then x = input(substr(name,x),best.);
run;

proc sql noprint;
select name into :varnames separated by ' ' from names order by x;
quit;

data want;
format &varnames;
set yourds;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,997

Re: Proc SQL - Ordering Column Names

Posted in reply to KurtBremser

Nice.  Though to be fair, it is better to model the data in a way which allows multiple rows per block of data rather than across the page, this is pretty standard in CDISC models for instance.  For example this data could be represented:

ID  SEQ  NAME  LAST  AGE
1    1   abc   temp   12
1    2   def   temp2  43
1    3   efg   temp3  34

In this method the information is all present, easy to work with, and is expandable infinitely (almost), whereas columns run out or become unwieldy and coding is far harder.

Super User
Posts: 7,868

Re: Proc SQL - Ordering Column Names


RW9 wrote:

Nice.  Though to be fair, it is better to model the data in a way which allows multiple rows per block of data rather than across the page, this is pretty standard in CDISC models for instance.  For example this data could be represented:

ID  SEQ  NAME  LAST  AGE
1    1   abc   temp   12
1    2   def   temp2  43
1    3   efg   temp3  34

In this method the information is all present, easy to work with, and is expandable infinitely (almost), whereas columns run out or become unwieldy and coding is far harder.


Of course the way to go. Normalized data is to be preferred. Intelligent data makes for intelligent programs.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 4 replies
  • 304 views
  • 6 likes
  • 3 in conversation