Help using Base SAS procedures

How to put the variables in alphabetical order in a dataset

Reply
N/A
Posts: 0

How to put the variables in alphabetical order in a dataset

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


The help will be appreciated.
Regular Contributor
Posts: 191

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

Posted in reply to deleted_user
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
Respected Advisor
Posts: 3,799

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

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]
Regular Contributor
Posts: 191

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

Posted in reply to data_null__
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
Respected Advisor
Posts: 3,799

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

> 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]
Regular Contributor
Posts: 191

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

Posted in reply to data_null__
Saw my misstake, always like that with quick fixes and tests.....great, it workes fine!
Thanks!
Nice macro thou'.....isn't it...Smiley Happy
Respected Advisor
Posts: 3,799

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

> Nice macro thou'.....isn't it...Smiley Happy
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]
N/A
Posts: 0

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

Posted in reply to deleted_user
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
Regular Contributor
Posts: 229

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

Posted in reply to deleted_user
it worked
N/A
Posts: 0

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

THis worked for me, but I drop off the last 8 columns. Any advice?
Super Contributor
Posts: 474

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

Posted in reply to deleted_user
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.
Ask a Question
Discussion stats
  • 10 replies
  • 1531 views
  • 0 likes
  • 5 in conversation