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
PROC Star

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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