Howdy,
I'm trying to use a variable in the AS part of my SQL Statement but not having any luck. Here is an example:
PROC SQL;
CREATE TABLE newtbl AS
SELECT 1 AS 'Year Is (&REPORT_YEAR.)'N
FROM SomeTable
;QUIT;
Does anyone know how to get the variable to render its value in the column name?
You cannot use a VARIABLE to make a variable name.
You could use a SYMBOL (aka a macro variable). So if you have the value in a variable then first put it into a macro variable.
But the macro processor will ignore strings inside of single quotes.
If the value of the macro variable REPORT_YEAR is already a valid SAS name (letter,digits and underscore and does not start with a digit) then just use it directly.
%let report_year=2023;
proc sql;
create table newtbl as
select 1 as Year_is_&report_year.
, *
from sometable
;
quit;
If you like that string with the spaces and () then perhaps you can use it as the LABEL on the variable instead?
%let report_year=2023;
proc sql;
create table newtbl as
select 1 as report_year label="Year is (&report_year.)"
, *
from sometable
;
quit;
If you really what to make the dataset hard to work with by using non SAS names for the variable name then use double quotes to make the name literal.
%let report_year=2023;
proc sql;
create table newtbl as
select 1 as "Year is (&report_year.)"n
, *
from sometable
;
quit;
That invalid name will not work unless you have set the system option VALIDVARNAME to ANY instead of the normal V7 setting.
Why do you want to use a label as variable name?
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.