topic Re: Order variables in dataset in SAS Procedures

Order variables in dataset

blackraven — Wed, 22 Sep 2010 10:57:33 GMT

Hi.

I would like to order variables in alphabetic/numeric order, e.g. a1, a1a, a2, a10a, a11.

If I use proc contents (keep = name), then proc sql to put the variables into a macro variable and then use retain in a data procedure, it will not put the above variables in correct order.

Could it be solved in another way?

Re: Order variables in dataset

art297 — Wed, 22 Sep 2010 11:51:49 GMT

Will you get what you want if you first sort the results of your proc contents? Depending upon the order you want, you may need 9.2 and have to use:
SORTSEQ=linguistic(NUMERIC_COLLATION=ON) when using proc sort.

HTH,
Art

Re: Order variables in dataset

ieva — Wed, 22 Sep 2010 11:56:34 GMT

Hi,

At first, you should be careful with retain as there could be some variables with missing values and if you use retain you could get unexpected values.

Actually retain should put variables in correct order, only then you should put this retain before set statement. Another way – you could use format statement instead:

data one;
b=1;
a=2;
run;

data two;
format a b;
set one;
run;

But then you should manually define variables and their formats if necessary. I think there should be some more elegant method, but if you don‘t have too many variables, this one could work, only you have to do that manually.

Ieva

Re: Order variables in dataset

samHT — Wed, 22 Sep 2010 12:13:08 GMT

Hi,

Use Proc contents to get the list of variables, then use below sql code, which creates new SAS table called sample_t from exitsing sample table, with variables in the order u mentioned in select statement

proc sql ;
create table sample_t as
select a1, a2, a3, a4,........a10, a11
from sample;
quit;

Re: Order variables in dataset

Patrick — Wed, 22 Sep 2010 13:02:15 GMT

Hi

I got lately sick of typing especially in SQL select clauses with same named variables from different tables (like match keys).

I therefore created a macro which does all this typing for me.

Hope it will be of some use for you.

options nomprint nomlogic nosymbolgen;

%macro AttrList(LibAndTable,KeepList=,DropList=);

%global SAS_DSAttrList SAS_SQLColumnList;
%local KeepListComma DropListComma;

%let LibAndTable=%upcase(&LibAndTable);
%let KeepList=%cmpres(%upcase(&KeepList));
%let DropList=%cmpres(%upcase(&DropList));

%let KeepListComma=%sysfunc(prxchange(%str(s/\b +\b/','/oi), -1, &KeepList));
%if %bquote(&KeepListComma) ne %bquote() %then %let KeepListComma=%str(%')&KeepListComma%str(%');

%let DropListComma=%sysfunc(prxchange(%str(s/\b +\b/','/oi), -1, &DropList));
%if %bquote(&DropListComma) ne %bquote() %then %let DropListComma=%str(%')&DropListComma%str(%');

%put DropListComma= &DropListComma;

proc datasets lib=work nolist nowarn memtype=(data view);
delete __VarAndAttrListTBL;
quit;

proc sql noprint;

/* create work table __VarAndAttrListTBL containing variable names and attributes */
create table __VarAndAttrListTBL as
select
varnum
,name
,type
,length
,format
,informat
,label

from dictionary.columns

where libname="%scan(WORK.&LibAndTable,-2,'.')"
and memname="%scan(&LibAndTable,-1,'.')"
%if %bquote(&KeepList) ne %bquote() %then
%do;
and upcase(name) in (%unquote(&KeepListComma))
%end;
%if %bquote(&DropList) ne %bquote() %then
%do;
and upcase(name) not in (%unquote(&DropListComma))
%end;

/* order by varnum*/
order by name
;

/* create macro var &SAS_DSAttrList containing list of vars like needed for Data step Attrib statement */
select
catx(' ',
name
,case upcase(type)
when 'NUM' then cats('length=',length)
else cats('length=$',length)
end
,case
when format ne '' then cats('format=',format)
else ''
end
,case
when informat ne '' then cats('informat=',informat)
else ''
end
,case
when label ne '' then cats("label='",label,"'")
else ''
end
)
into :SAS_DSAttrList separated by ' '

from __VarAndAttrListTBL
/* order by varnum*/
order by name
;

/* create macro var &SAS_SQLSelectList containing list of vars like needed for SAS SQL Select clause */
select
catx(' ',
name
,cats('length=',length)
,case
when format ne '' then cats('format=',format)
else ''
end
,case
when informat ne '' then cats('informat=',informat)
else ''
end
,case
when label ne '' then cats("label='",label,"'")
else ''
end
)
into :SAS_SQLColumnList separated by ' ,'

from __VarAndAttrListTBL
/* order by varnum*/
order by name
;

quit;

%put SAS_DSAttrList = &SAS_DSAttrList;
%put SAS_SQLColumnList = &SAS_SQLColumnList;

%mend;

data have;
a2=1;
a1=1;
run;

%AttrList(work.have)

data want;
attrib &SAS_DSAttrList;
set have;
run;

proc contents data=want;
quit;

Cheers
Patrick

Had to change 'order by varnum' to 'order by name' to cover for this requirement

Message was edited by: Patrick

Re: Order variables in dataset

chang_y_chung_hotmail_com — Wed, 22 Sep 2010 14:08:44 GMT

...
> I would like to order variables in alphabetic/numeric
> order, e.g. a1, a1a, a2, a10a, a11.
>
> If I use proc contents (keep = name), then proc sql
> to put the variables into a macro variable and then
> use retain in a data procedure, it will not put the
> above variables in correct order.
...
This sounds like a reasonable approach. Below surely is. IMHO, that is. 🙂
[pre]
/* test data */
data one;
retain a1 a10a a11 a1a a2 .;
run;

/* prepare an ordered list of vars */
%let num = 0123456789;
%let ordered=;

proc sql noprint;
select name into :ordered separated by " "
from dictionary.columns
where libname="WORK" and memname="ONE"
order by scan(name, 1, "&num")
, inputn(scan(name, 1, "&num", "k"), "best")
, scan(name, 2, "&num");
quit;

/* re-order */
data two;
retain &ordered;
set one;
run;

/* check */
proc contents data=two order=varnum;
run;
/* on lst -- in part
# Variable Type Len
1 a1 Num 8
2 a1a Num 8
3 a2 Num 8
4 a10a Num 8
5 a11 Num 8
*/
[/pre]

Re: Order variables in dataset

blackraven — Wed, 22 Sep 2010 17:11:40 GMT

Thanks, guys. Already the first proc sort option did it. I will check the others as well.

Re: Order variables in dataset

scmebu — Wed, 22 Sep 2010 19:21:42 GMT

Patrick's macro and SQL solution can be modified to create the same order as PROC SORT with NUMERIC_COLLATION=ON by using the SORTKEY function:

order by sortkey(name,'','','','N')