PROC SQL Macro Variables (INTO Statement) - with ORDER BY Statement

[djbateman]

I am trying to store some values into some macro variables.  My problem is that the step I am using automatically sorts the values alphabetically instead of in the order that I want.  When I add an ORDER BY statement, I get a warning in the log.  Here is an example of what I am doing (the dataset is a subset of information from SASHELP.VCOLUMN):

data ec1456;

      length libname $8 memname $32 name $32 varnum 8;

      input libname $ memname $ name $ varnum;

      cards;

EC1456      AE    AESTDT_RAW  44

EC1456      AE    AEENDT_RAW  50

EC1456      AE    AECHGDT_RAW 81

;

run;

proc sql;

      /***  Orders the variables alphabetically  ***/

      select distinct name into :var1-:var3 from ec1456;

      /***  Orders the variables by VARNUM but leaves a warning message  ***/

      select distinct name into :var1-:var3 from ec1456 order by varnum;

quit;

I have read that I can just add NOWARN to the PROC SQL statement, but I have also been warned that it should not be used in production code.  Those forums were also written in 2009, so I thought that maybe things have advanced in the last 5 years.  Any thoughts on how I can properly sort the variables without getting a warning message?

[Reeza · Accepted solution]

Sort before selecting and create via a data step instead.

Create an empty variable that is useless and include it as well, but this may result in a different answer if you have repeated names across varnum.

proc sql noprint;

      /***  Orders the variables alphabetically  ***/

      select distinct name into :var1-:var3 from ec1456;

      /***  Orders the variables by VARNUM but leaves a warning message  ***/

      select distinct varnum, name into :empty, :var1-:var3 from ec1456 order by varnum;

quit;

[Astounding]

When using DISTINCT, the software has to accumulate a list of all values before putting them into macro variables.  That's what causes alphabetizing.  If you already have only distinct values in your data, it would be easy enough to remove DISTINCT from your code.  Otherwise, the solution becomes complicated.

[Tom]

To suppress the warning about using a variable to ORDER BY that is not in the SELECT list you can pull that one also.

I have found that you can reuse the same macro variable name.

proc sql noprint ;

select varnum

      , name

   into :name1

      , :name1 - :name99999

   from dictionary.columns

   where libname='SASHELP' and memname='CLASS'

;

%let nvars=&sqlobs ;

%put _user_;

GLOBAL NAME1 Name

GLOBAL NAME2 Sex

GLOBAL NAME3 Age

GLOBAL NAME4 Height

GLOBAL NAME5 Weight

GLOBAL NVARS 5

GLOBAL SQLEXITCODE 0

GLOBAL SQLOBS 5

GLOBAL SQLOOPS 27

GLOBAL SQLRC 0

GLOBAL SQLXOBS 0

GLOBAL SYS_SQL_IP_ALL -1

GLOBAL SYS_SQL_IP_STMT