BookmarkSubscribeRSS Feed
Randy2101
Calcite | Level 5

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?

2 REPLIES 2
Tom
Super User Tom
Super User

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
Jade | Level 19

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1000 views
  • 4 likes
  • 3 in conversation