topic Re: Proc SQL - Ordering Column Names in SAS Programming

Proc SQL - Ordering Column Names

CamRutherford — Mon, 16 Oct 2017 10:18:06 GMT

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

Re: Proc SQL - Ordering Column Names

RW9 — Mon, 16 Oct 2017 10:34:34 GMT

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;

Re: Proc SQL - Ordering Column Names

Kurt_Bremser — Mon, 16 Oct 2017 10:43:46 GMT

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;

Re: Proc SQL - Ordering Column Names

RW9 — Mon, 16 Oct 2017 10:49:17 GMT

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.

Re: Proc SQL - Ordering Column Names

Kurt_Bremser — Mon, 16 Oct 2017 10:52:29 GMT

@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.