BookmarkSubscribeRSS Feed
CamRutherford
Fluorite | Level 6

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

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Kurt_Bremser
Super User

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;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1225 views
  • 6 likes
  • 3 in conversation