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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.