topic Selecting into macro variables with ordering in SAS Programming

Selecting into macro variables with ordering

Tom — Sat, 23 Nov 2013 17:01:43 GMT

Have you ever received this note in the SAS log when using the SELECT ... INTO ... syntax of PROC SQL?

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

For example if you want to generate a list of variables from DICTIONARY.COLUMNS (or PROC CONTENTS output dataset) and preserve the data set order.

proc sql noprint ;

select name

into :varlist separated by ' '

from dictionary.columns

where libname='SASHELP'

and memname='CLASS'

order by varnum

;

quit;

%put nvars=&sqlobs varlist=&varlist;

Generates this output:

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

xxxx %put nvars=&sqlobs varlist=&varlist;

nvars=5 varlist=Name Sex Age Height Weight

What I used to do was to add the ordering variable (VARNUM) to the select clause and use a dummy macro variable to hold the result. But you can instead re-use the same macro variable if you set the order of the variables in the SELECT clause properly.

proc sql noprint ;

select varnum

, name

into :varlist

,:varlist separated by ' '

from dictionary.columns

where libname='SASHELP'

and memname='CLASS'

order by varnum

;

quit;

%put nvars=&sqlobs varlist=&varlist;

Now the NOTE no longer appears in the SAS log.

Re: Selecting into macro variables with ordering

Haikuo — Sun, 24 Nov 2013 16:09:06 GMT

Thanks for sharing, Tom. I was kinda wondering if no varnum involved, and no 'order by', what kind of order would it be? Randomly?

Haikuo

Re: Selecting into macro variables with ordering

Tom — Sun, 24 Nov 2013 16:33:38 GMT

SAS will normally preserve the order from the source table when querying with SQL, but it is not guaranteed.

If the underlying table is actually coming form a database system (such as Oracle, SQL Server, etc) then it is most likely that there is not a predefined order or even a consistent order for repetitions of the same query. Perhaps as SAS becomes more multi-threaded this will also be true for SAS datasets.