topic Sorting by variable name in SAS Procedures

Sorting by variable name

PTD_SAS — Fri, 24 Feb 2012 02:43:54 GMT

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.

Re: Sorting by variable name

Haikuo — Fri, 24 Feb 2012 03:10:05 GMT

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

Sorting by variable name

PTD_SAS — Fri, 24 Feb 2012 03:18:27 GMT

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

Sorting by variable name

Haikuo — Fri, 24 Feb 2012 03:44:26 GMT

"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.

Sorting by variable name

art297 — Fri, 24 Feb 2012 03:47:10 GMT

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;

Sorting by variable name

Tom — Fri, 24 Feb 2012 03:48:35 GMT

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?

Sorting by variable name

PTD_SAS — Fri, 24 Feb 2012 03:52:37 GMT

Tom,

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

Sorting by variable name

art297 — Fri, 24 Feb 2012 04:00:03 GMT

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.

Sorting by variable name

Haikuo — Fri, 24 Feb 2012 04:15:56 GMT

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

Sorting by variable name

PTD_SAS — Fri, 24 Feb 2012 04:35:13 GMT

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.

Sorting by variable name

PTD_SAS — Fri, 24 Feb 2012 04:38:25 GMT

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