Unable to get value instead column name is printed

[udupa_13]

Dataset has Year column, followed by 53 columns for each week starting from EH_WEEK_1, EH_WEEK_2 and so on upto EH_WEEK_53. I am trying to get the value under that particular week on specifying date. For eg,if I give as 29Sep2020 I am able to identify the week as 40 using WEEK function and then pass it through a macro variable and concatinate it with string "EH_WEEK_".But how do I get that value under EH_WEEK_40 by making it dynamic. Below is the snippet of the same:

%LET WeekEnd =  cats("EH_WEEK_", WEEK("29Sep2020"d,'V'));

PROC SQL;

CREATE TABLE WEEK_VALS as

SELECT YEAR, &WeekEnd as Weekly_Value

FROM FIT_TABLE

WHERE (conditions)

QUIT;

I get that while selecting column it is just passing the value from &WeekEnd variable which is "EH_WEEK_40" and the entire column is printed but how to get the value under EH_WEEK_40 column here?

[Tom]

SQL is doing what you asked it to do. 

PROC SQL;
CREATE TABLE WEEK_VALS as
  SELECT YEAR
       ,  cats("EH_WEEK_", WEEK("29Sep2020"d,'V')) as Weekly_Value
  FROM FIT_TABLE
;
QUIT;

What code did you want it to run for that 2SEP2020 date?

Did you mean to run this code?

PROC SQL;
CREATE TABLE WEEK_VALS as
  SELECT YEAR
       ,  EH_WEEK_40 as Weekly_Value
  FROM FIT_TABLE
;
QUIT;

In that case you would need to put the value EH_WEEK_40 into the macro variable.

%LET WeekEnd =  EH_WEEK_%sysfunc(week("29Sep2020"d,V));

[udupa_13]

Thanks for the solution. I tried sysfunc before in a different way but was concatenating in the end.