Selecting into macro variables with ordering

[Tom]

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.

[Haikuo]

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

[Tom]

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.