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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: