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?

hackathon24-white-horiz.png

The 2025 SAS Hackathon Kicks Off on June 11!

Watch the live Hackathon Kickoff to get all the essential information about the SAS Hackathon—including how to join, how to participate, and expert tips for success.

YouTube LinkedIn

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
  • 1614 views
  • 4 likes
  • 3 in conversation