DATA Step, Macro, Functions and more

Selecting into macro variables with ordering

Reply
Super User
Super User
Posts: 7,060

Selecting into macro variables with ordering

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.

Respected Advisor
Posts: 3,156

Re: Selecting into macro variables with ordering

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

Super User
Super User
Posts: 7,060

Re: Selecting into macro variables with ordering

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.

Ask a Question
Discussion stats
  • 2 replies
  • 466 views
  • 1 like
  • 2 in conversation