topic Sorting (ordering) variables of a data set in SAS Programming

Sorting (ordering) variables of a data set

tesu — Mon, 18 Nov 2013 22:17:36 GMT

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!

Re: Sorting (ordering) variables of a data set

Reeza — Mon, 18 Nov 2013 22:39:40 GMT

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.

Re: Sorting (ordering) variables of a data set

Anotherdream — Mon, 18 Nov 2013 23:18:46 GMT

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;

Re: Sorting (ordering) variables of a data set

Patrick — Mon, 18 Nov 2013 23:54:06 GMT

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

Re: Sorting (ordering) variables of a data set

tesu — Tue, 19 Nov 2013 00:02:48 GMT

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!

Re: Sorting (ordering) variables of a data set

Anotherdream — Tue, 19 Nov 2013 00:16:48 GMT

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;

Re: Sorting (ordering) variables of a data set

Patrick — Tue, 19 Nov 2013 00:52:34 GMT

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;