special characters in alias Proc sql- SAS 9.3

1
votes

I need to have a special character (% and space) in the alsias name of a proc sql statement.

proc sql DQUOTE=ANSI;
create table final_data as
select a.column1 as XYZ, 
((a.colum2/b.colum2)-1) as "% VS LY"
from table1 a
join table2 b on a.colum3=b.colum3;
quit;

according to the documention, having the option proc sql DQUOTE=ANSI should work..

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001393333.htm

However, I'm getting this error in SAS 9.3

ERROR: The value % VS LY is not a valid SAS name.

What should I do to make this work?

Thank you so much in advance!

2
sassas-macroenterprise-guide

2 Answers

1
votes

Perhaps a simpler solution would be to use standard naming and a SAS label. If the computed value is between 0 and 1 you can also add a SAS format.

((a.colum2/b.colum2)-1) as vs_ly_pct label='% VS LY' format=percent5.2

If you truly want non-standard column names, you will also need to set

options validvarname = any;

before the Proc SQL.

0
votes

In SQL an alias is what you use to prefix variable references to tell which input table (or subquery) the variable comes from. Like the a and b in your query. What you are talking about is the variable NAME.

SAS variable names normally are restricted to underscore and alphanumeric characters (and cannot start with a number), but variable LABELS can be any string. You can just specify the label after the name.

select a.column1 as XYZ
     , ((a.colum2/b.colum2)-1) as var2 '% VS LY'

Or use the SAS specific LABEL= syntax 

select a.column1 as XYZ
     , ((a.colum2/b.colum2)-1) as var2 label='% VS LY'