topic PROC SQL Macro Variables (INTO Statement) - with ORDER BY Statement in ODS and Base Reporting

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

djbateman — Fri, 25 Apr 2014 19:24:22 GMT

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?

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

Reeza — Fri, 25 Apr 2014 19:34:21 GMT

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;

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

Astounding — Fri, 25 Apr 2014 19:40:14 GMT

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.

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

Tom — Fri, 25 Apr 2014 21:28:50 GMT

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