Using a variable in PROC SQL AS

[Randy2101]

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?

[Tom]

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.

[andreas_lds]

Why do you want to use a label as variable name?