topic Re: sorting variable names in a sas dataset ? in SAS Programming

sorting variable names in a sas dataset ?

Pavan_SAS — Fri, 01 Aug 2008 12:15:38 GMT

i have 100 variables in a sas dataset.
i want to see those variables in ascending order of their names, in that dataset.

note that, i am trying to sort variable names, instead of observations(proc sort).

is it possible in sas?

another doubt:-

in those 100 variables, i want to see 98th variable as 2nd variable.
becas it is frequently used one.
means, placing perticular variable at required position.

plz help me !

regards,
pavan.

Re: sorting variable names in a sas dataset ?

darrylovia — Fri, 01 Aug 2008 13:07:07 GMT

Well not easy to do you can have a macro do it.

by creating a new table in proc sql you can reorder the columns. See the below macro for guidance.

%macro ReorderColumns(in_lib,input_dataset_name, output_dataset);

proc sql;
create table VARNAMES as
select upcase(NAME) as NAME, varnum
from DICTIONARY.COLUMNS
where libname=upcase("&in_lib") and memname=upcase("&input_dataset_name")
order by upcase(NAME);
quit;

data _null_;
set VARNAMES end=eof;
if not eof then call symput('varname'||strip(put(_n_,8.)),strip(name)||',');
else do;
call symput('varname'||strip(put(_n_,8.)),strip(name));
call symputx('numvar',_n_);
end;
run;

proc sql;
create table &output_dataset as
select %do i=1 %to &numvar;
&&varname&i
%end;
from &in_lib..&input_dataset_name ;
quit;

%mend ReorderColumns;

%ReorderColumns(sashelp,shoes,work.test);

proc print data=sashelp.shoes;
proc print data=test;run;

Regards!
-Darryl

Re: sorting variable names in a sas dataset ?

sbb — Fri, 01 Aug 2008 17:54:45 GMT

Here is some PROC SQL code I frequently use to generate a sorted variable list of periods (PDyymmdd -- output from PROC TRANSPOSE) as columns, output as a SAS macro variable. Do consider that there is a max length for a macro variable, which may limit use of this technique:

data TEST_DATA;
RETAIN PD200801-PD200807 2.2222 A1 'xxxxxx';
RUN;
proc sql noprint;
select name into :PDLIST separated by ' '
from dictionary.columns
where upcase(libname) = 'WORK'
and upcase(memname) = 'TEST_DATA'
and upcase(name) like 'PD%'
order by name descending;
quit;
%put sorted var list (descending): &pdlist;
proc print u ;
id a1; var &pdlist;
run;

Scott Barry
SBBWorks, Inc.