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.
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.