topic How to rearrange the columns' order alphabetically in SAS Procedures

How to rearrange the columns' order alphabetically

George_S — Fri, 30 Mar 2012 16:17:19 GMT

Is there any better way to rearrange the variables in alphabetically order?

data ask;

a_555=1;c_666=3;b_111=2;

run;

Re: How to rearrange the columns' order alphabetically

MikeZdeb — Fri, 30 Mar 2012 16:30:27 GMT

hi ... one idea ...

data old;
a_555=1;
c_666=3;
b_111=2;
run;

proc sql noprint;
select name into :vars separated by ' '
from dictionary.columns
where libname eq 'WORK' and memname eq 'OLD'
order by name;
quit;

data old;
retain &vars;
set old;
run;

How to rearrange the columns' order alphabetically

OS2Rules — Fri, 30 Mar 2012 16:32:27 GMT

You may want to hhave a look at this trhead - I needed a similar process for an FSVIEW ..

https://communities.sas.com/message/102658#102658

(works brilliantly BTW).

How to rearrange the columns' order alphabetically

data_null__ — Fri, 30 Mar 2012 17:33:21 GMT

If there are enumerated list variables a1-a100 for example I don't think this will order the variables in a pleasing way. There is a PROC SORT option that I can't think of right now that would work but I don't know how or if that can be implement in SQL orderby.

PROC CONTENS may output the names in the proper order by default I can't remember. There is ORDER= option.

Re: How to rearrange the columns' order alphabetically

MikeZdeb — Fri, 30 Mar 2012 17:49:09 GMT

hi ... correct (as usual), so (I don't know how to use the different sort options within SQL) ...

data old;
z2=10; a_555=1; c_666=3; z1=9; b_111=2; z100=10;

run;

proc sql noprint;
create table vars as
select name from dictionary.columns
where libname eq 'WORK' and memname eq 'OLD';
quit;

proc sort data=vars sortseq=linguistic(numeric_collation=on);
by name;
run;

proc sql noprint;
select name into :vars separated by ' ' from vars;

drop table vars;
quit;

data old;
retain &vars;
set old;
run;

How to rearrange the columns' order alphabetically

art297 — Fri, 30 Mar 2012 17:50:33 GMT

DN: I think you are referring to numeric_collation

proc sort data=one out=two

sortseq=linguistic (numeric_collation=on);

by bank;

run;

but I don't think it is avaiable in proc sql. However, Chang Chung proposed some code in a thread a couple of years ago:

This sounds like a reasonable approach. Below surely is. IMHO, that is.

Re: Order variables in dataset

/* test data */

data one;

retain a1 a10a a11 a1a a2 .;

run;

/* prepare an ordered list of vars */

%let num = 0123456789;

%let ordered=;

proc sql noprint;

select name into :ordered separated by " "

from dictionary.columns

where libname="WORK" and memname="ONE"

order by scan(name, 1, "&num")

, inputn(scan(name, 1, "&num", "k"), "best")

, scan(name, 2, "&num");

quit;

/* re-order */

data two;

retain &ordered;

set one;

run;

/* check */

proc contents data=two order=varnum;

run;

/* on lst -- in part

Variable Type Len

1 a1 Num 8

2 a1a Num 8

3 a2 Num 8

4 a10a Num 8

5 a11 Num 8

How to rearrange the columns' order alphabetically

data_null__ — Fri, 30 Mar 2012 18:07:29 GMT

The default out= from PROC CONTENTS or perhaps ORDER=IGNORECASE is probably the most pleasing order with regards to enumerated lists. No name fiddling required.

Re: How to rearrange the columns' order alphabetically

MikeZdeb — Fri, 30 Mar 2012 18:34:24 GMT

hi ... hmmm, I think that depends ...

if you want all the q_2 vars listed between q_1 and q_10 ...

data old;
retain q_1 q_34 q_2a q_2 q_2x q_10 0;
run;

CONTENTS doesn't work, but NUMERIC COLLATION does

I guess one has to make a choice

Re: How to rearrange the columns' order alphabetically

data_null__ — Fri, 30 Mar 2012 19:02:32 GMT

proc contents treats enumerated variables in a "special way" even if some of them are not present. Variables that end in characters are not enumerated variables.

Like you say depends on what you want.

Re: How to rearrange the columns' order alphabetically

Ksharp — Sat, 31 Mar 2012 01:59:14 GMT

How about:

This solution is from Howles.

data old;
z2=10; a_555=1; c_666=3; z1=9; b_111=2; z100=10;
run;

 

proc sql noprint;
select name into : list separated by ' ' from dictionary.columns
 where libname eq 'WORK' and memname eq 'OLD'
  order by input(compress(name, ,'kd'),best8.);
quit;
%put &list;

Ksharp