DATA Step, Macro, Functions and more

Sorting (ordering) variables of a data set

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Sorting (ordering) variables of a data set

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!


Accepted Solutions
Solution
‎11-18-2013 07:52 PM
Respected Advisor
Posts: 4,173

Re: Sorting (ordering) variables of a data set

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;

View solution in original post


All Replies
Super User
Posts: 19,851

Re: Sorting (ordering) variables of a data set

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.

Super Contributor
Posts: 418

Re: Sorting (ordering) variables of a data set

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;

Respected Advisor
Posts: 4,173

Re: Sorting (ordering) variables of a data set

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

Contributor
Posts: 40

Re: Sorting (ordering) variables of a data set

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!

Super Contributor
Posts: 418

Re: Sorting (ordering) variables of a data set

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;

Solution
‎11-18-2013 07:52 PM
Respected Advisor
Posts: 4,173

Re: Sorting (ordering) variables of a data set

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 293 views
  • 1 like
  • 4 in conversation