Sorting (ordering) variables of a data set

[tesu]

Hi all,

I have a data set with over 500 variables, and they are unordered. Currently, by using Proc Contents, I extract the ordered list of variables, copy the list from the html output, paste it onto the Excel, delete the unnecessary columns, and copy again the list. Then, I do the following:

proc mydata;

     retain

     (paste the list of variables..)

     ;

     set mydata;

run;

Is there any other method to do this with avoiding copying and pasting things? Thanks!

[Reeza]

The general process is the same but you can automate it by using the sashelp.vcolumn list and macro variables.

There's a lot of posts on how to accomplish this on here so some searching should get you close to what you want.

[Anotherdream]

proc contents data=YOURDATA out=newdataset;

run;

proc sort data=newdataset;

by varnum;

run;

data newdatasetDROP;

set newdataset;

where upcase(NAME) not in ("YOUR VARIABLE NAME LIST");

run;

proc sq;

select name into : orderedlist separated by ' '

from newdatasetDROP;

quit;

run;

data answer;

retain &orderedlist;

set YOURDATASET:

run;

[Patrick]

Are the variables you want or don't want following some naming pattern (eg. all unwanted variables start with 'var...')?

[tesu]

Hi Patrick,

If my data looks like:

data nordered;

input c b a;

cards;

10 555 8762

11 877 9748

12 898 2874

;run;

Then, my data should be changed as:

Obs      a      b      c
1     8762    555    10

2     9748    877    11

3     2874    898    12

I don't have any other variable selection criteria. Thanks!

[Anotherdream]

The issue is you haven't specified how you want them ordered. In the above example it is alphabetical, but will that always hold true?

If so, then the code I gave above will work, the only difference is instead of sorting by varnum, you sort by Name. Aka the following.

proc contents data=YOURDATA out=newdataset;

run;

proc sort data=newdataset;

by NAME;

run;

data newdatasetDROP;

set newdataset;

where upcase(NAME) not in ("YOUR VARIABLE NAME LIST");

run;

proc sq;

select name into : orderedlist separated by ' '

from newdatasetDROP;

quit;

run;

data answer;

retain &orderedlist;

set YOURDATASET:

run;

[Patrick · Accepted solution]

data nordered;
  input c b a;
  label b='just a test';
  cards;
10 555 8762
11 877 .
12 898 2874
;
run;

proc sql noprint;
  select name into :varlist separated by ' '
    from dictionary.columns
    where libname='WORK' and memname='NORDERED'
    order by name
  ;
quit;

data work.nordered;
  retain &varlist;
  set nordered;
run;

proc contents data=work.nordered;
run;