topic Re: How to put the variables in alphabetical order in a dataset in SAS Procedures

How to put the variables in alphabetical order in a dataset

deleted_user — Tue, 24 Feb 2009 11:33:46 GMT

I have to change the dataset in such a way that all the variables should be in alphabetical order.

The help will be appreciated.

Re: How to put the variables in alphabetical order in a dataset

FredrikE — Tue, 24 Feb 2009 13:55:06 GMT

HI!
The easy way is to put length before set:

data test;
length Age Height 8 Name $8 Sex $1 Weight 8;
set sashelp.class;
run;

Works if you know alll variables, else you can create length statment using proc contents and macro.

//Fredrik

Re: How to put the variables in alphabetical order in a dataset

data_null__ — Tue, 24 Feb 2009 14:00:29 GMT

You don't want to use a LENGTH statement because you don't need to know those attributes and you don't want to fiddle them. Too much trouble.

You just need alphabetical list and RETAIN.

[pre]
proc sql noprint;
select name into :vars separated by ' '
from dictionary.columns
where libname eq 'SASHELP' and memname eq 'CLASS'
order by name
;
quit;
run;
%put NOTE: VARS=&vars;

data work.class;
retain &vars;
set sashelp.class;
run;
proc print;
run;
[/pre]

Re: How to put the variables in alphabetical order in a dataset

FredrikE — Tue, 24 Feb 2009 14:30:47 GMT

Your example works if all obs has values on every var, try this:
data test;
length b a g d 8;
b = 1; a = 2; d=10; g = 30;output;
b = 2; g = 31;output;
b = 3; a = 9; d=7; g = 32;output;
b = 4; g = 33;output;
run;

data test2;
retain a b d g;
set test;
run;
proc print data = test2;run;

retain is used for retaining vars....I think...:-)

I made a small macro that i hope works , even with work-tables...;-)

%macro sortvar(ds);
proc contents data = &ds out = metadata;run;
proc sort data = metadata; by name;run;

data _null_;
retain counter 0;
set metadata end=last;
counter+1;
call symput('VAR'||compress(put(counter,8.)),trim(name));
if type eq 1 then call symput('TYP'||compress(put(counter,8.)),trim(''));
else call symput('TYP'||compress(put(counter,8.)),trim('$'));
call symput('LEN'||compress(put(counter,8.)),trim(length));
if last then do;
call symput('NOVARS',compress(put(counter,8.)));
end;
run;

data &ds;
%DO I = 1 %TO &NOVARS;
length &&VAR&I &&TYP&I&&LEN&I;
%END;
set &ds;
run;
%mend;

/* example of usage, replace sashelp.class with your own dataset */
%sortvar(sashelp.prdsale);

//Fredrik

Re: How to put the variables in alphabetical order in a dataset

data_null__ — Tue, 24 Feb 2009 14:58:14 GMT

> Your example works if all obs has values on every
> var, try this:
> data test;
> length b a g d 8;
> b = 1; a = 2; d=10; g = 30;output;
> b = 2; g = 31;output;
> b = 3; a = 9; d=7; g = 32;output;
> b = 4; g = 33;output;
> un;
>
> data test2;
> retain a b d g;
> set test;
> run;
> proc print data = test2;run;
>
> retain is used for retaining vars....I think...:-)

This is a common misconception.

Your example is flawed but I have corrected it below to show that using RETAIN is "safe" for reordering variables read with a SET statement. RETAIN is for retaining values but RETAIN does not have any effect on the values of variables read using a SET statement other than to reorder them when RETAIN is placed before the SET statement.

[pre]
data test;
length b a g d 8;
b = 1; a = 2; d=10; g = 30;output;
call missing(of _all_);
b = 2; g = 31; output;
call missing(of _all_);
b = 3; a = 9; d=7; g = 32;output;
call missing(of _all_);
b = 4; g = 33; output;
run;
proc print;
run;
data test2;
retain a b d g;
set test;
run;
proc print;
run;
**Output from second PROC PRINT;
Obs a b d g

1 2 1 10 30
2 . 2 . 31
3 9 3 7 32
4 . 4 . 33

[/pre]

Re: How to put the variables in alphabetical order in a dataset

FredrikE — Tue, 24 Feb 2009 15:18:50 GMT

Saw my misstake, always like that with quick fixes and tests.....great, it workes fine!
Thanks!
Nice macro thou'.....isn't it...:)

Re: How to put the variables in alphabetical order in a dataset

data_null__ — Tue, 24 Feb 2009 15:46:34 GMT

> Nice macro thou'.....isn't it...:)
Well you don't need a macro to do this and if you did I would not create all those macro variables.

What your example does remind me of is the need for PROC CONTENTS as it provides alphabetical order by default, (you didn't need to sort), with special "attention" to SAS enumerated variable lists. (a1 followed by a2 not a10). This ordering is not obtained by a simple sort of the NAMES as in my first example using DICTIONARY.COLUMNS.

See new example.

[pre]
data test;
length b a g d 8 a4-a11 a1-a3 8;
b = 1; a = 2; d=10; g = 30;output;
call missing(of _all_);
b = 2; g = 31; output;
call missing(of _all_);
b = 3; a = 9; d=7; g = 32;output;
call missing(of _all_);
b = 4; g = 33; output;
run;
proc print;
run;
proc contents data=work.test out=work.names(keep=name);
run;
proc sql noprint;
select name into :vars seperated by ' '
from work.names
;
quit;
run;
%put NOTE: vars=&vars;

data test2;
retain &vars;
set test;
run;
proc contents varnum;
run;

Variables in Creation Order

# Variable Type Len

1 a Num 8
2 a1 Num 8
3 a2 Num 8
4 a3 Num 8
5 a4 Num 8
6 a5 Num 8
7 a6 Num 8
8 a7 Num 8
9 a8 Num 8
10 a9 Num 8
11 a10 Num 8
12 a11 Num 8
13 b Num 8
14 d Num 8
15 g Num 8
[/pre]

Re: How to put the variables in alphabetical order in a dataset

deleted_user — Wed, 25 Feb 2009 16:37:54 GMT

Here's another solution that's pretty easy:

/* Create a dataset containing a list of variable names */
proc contents data=adam.adsl out=contents (keep=name);
run;
/* Sort the list by variable name */
proc sort data=contents;
by name;
run;
/* Create a macro variable containing a string of variable names */
data _null_;
set contents end=eof;
length lst $200.;
retain lst;
lst = strip(lst) || " " || strip(name);
if eof then
call symput('lst',lst);
run;
/* Reference the macro variable in a retain statement */
data adsl_x;
retain &lst;
set adam.adsl;
run;

The only problem that might occur is if you have lots of variables and the list can't fit into a single string - but it's easy enough to create more than one string if necessary

Re: How to put the variables in alphabetical order in a dataset

R_Win — Thu, 26 Feb 2009 05:41:03 GMT

it worked

Re: How to put the variables in alphabetical order in a dataset

deleted_user — Thu, 23 Jul 2009 23:14:28 GMT

THis worked for me, but I drop off the last 8 columns. Any advice?

Re: How to put the variables in alphabetical order in a dataset

DanielSantos — Mon, 27 Jul 2009 07:45:23 GMT

And yet another solution:

proc sql noprint;
select name into :vars separated by ','
from dictionary.columns
where libname eq 'SASHELP' and memname eq 'CLASS'
order by name;
create table WORK.CLASS as
select &VARS from SASHELP.CLASS;
quit;

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.