Using a variable in PROC SQL AS

Randy2101 — Wed, 02 Aug 2023 20:42:14 GMT

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?

Re: Using a variable in PROC SQL AS

Tom — Wed, 02 Aug 2023 20:53:41 GMT

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.

Re: Using a variable in PROC SQL AS

andreas_lds — Thu, 03 Aug 2023 10:19:40 GMT

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

topic Using a variable in PROC SQL AS in SAS Programming

Using a variable in PROC SQL AS

Re: Using a variable in PROC SQL AS

Re: Using a variable in PROC SQL AS