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
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;
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;
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.
@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 34In 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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.