Help using Base SAS procedures

Sorting by variable name

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Sorting by variable name

Is there a way in SAS 9.3 to sort datasets alphabetically by variable names? I.e. each observation of the dataset to have the variables sorted alphabetically.

Thanks.


Accepted Solutions
Solution
‎02-23-2012 11:00 PM
Esteemed Advisor
Posts: 7,051

Sorting by variable name

Did you try my SQL solution?  The only thing you have to insure is that, in the call to dictionary.columns, that the libname and memname are spelled in CAPs.

View solution in original post


All Replies
Respected Advisor
Posts: 3,124

Re: Sorting by variable name

I am sure there will be better approaches, but given it is alreay 10pm est, you may not get them as timely as you wanted. So here is one option for you:

data have;

input b a c e d;

cards;

1 2 3 4 5

;

proc contents data=have out=have1;

run;

proc sql;

select name into :name separated by ' ' from have1;

quit;

data want;

retain &name;

set have;

run;

of course, one variation of this solution is to Proc SQL for the second half part of it:

proc sql;

select name into :name separated by ',' from have1;

quit;

proc sql;

create table want as select &name from have; quit;

Regards,

Haikuo

Contributor
Posts: 71

Sorting by variable name

Thanks for that! I'll try it although it'll be a bit awkward with many variable names.

Respected Advisor
Posts: 3,124

Sorting by variable name

"Many variable names", I hope not too many and too long. As long as it 's less than 32k characters after concatenation (including blanks), you will be fine.

Respected Advisor
Posts: 3,124

Sorting by variable name

Just found out that there is something worth mentioning of my solution: the case. You may want to add an option if you want to ignore the case:

proc contents data=have out=have1 order=ignorecase;

run;

otherwise it will put upper case variable names ahead of those lower case.

Haikuo

Contributor
Posts: 71

Sorting by variable name

Thanks again. I used art297's SQL solution, it works very well.

Esteemed Advisor
Posts: 7,051

Sorting by variable name

There is always the pure SQL solution:

proc sql noprint;

  select name

    into :names separated by ","

    from dictionary.columns

      where libname="SASHELP" and

            memname="CLASS"

       order by name

  ;

  create table want

    as select &names.

      from sashelp.class

  ;

quit;

Super User
Super User
Posts: 6,121

Sorting by variable name

What does that mean?  Normally when you talk of sorting a dataset you mean sorting the observations.

Are you saying you want to create a copy of the data with the variables ordered alphabetically by their name rather than their original position?

Contributor
Posts: 71

Sorting by variable name

Tom,

Yes, exactly. I want to have the dataset with the variables ordered alphabetically by their name.

Solution
‎02-23-2012 11:00 PM
Esteemed Advisor
Posts: 7,051

Sorting by variable name

Did you try my SQL solution?  The only thing you have to insure is that, in the call to dictionary.columns, that the libname and memname are spelled in CAPs.

Contributor
Posts: 71

Sorting by variable name

That's excellent!, it worked. It made my job much easier because I analyse large datasets in JMP and now I can locate a specific variable name very easily.

Thanks.

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 651 views
  • 3 likes
  • 4 in conversation