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?
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;
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;
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.
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
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.