BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I have to change the dataset in such a way that all the variables should be in alphabetical order.


The help will be appreciated.
10 REPLIES 10
FredrikE
Rhodochrosite | Level 12
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
data_null__
Jade | Level 19
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]
FredrikE
Rhodochrosite | Level 12
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
data_null__
Jade | Level 19
> 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]
FredrikE
Rhodochrosite | Level 12
Saw my misstake, always like that with quick fixes and tests.....great, it workes fine!
Thanks!
Nice macro thou'.....isn't it...:)
data_null__
Jade | Level 19
> 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]
deleted_user
Not applicable
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
deleted_user
Not applicable
THis worked for me, but I drop off the last 8 columns. Any advice?
DanielSantos
Barite | Level 11
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 7935 views
  • 1 like
  • 5 in conversation