topic Unable to get value instead column name is printed in SAS Programming

Unable to get value instead column name is printed

udupa_13 — Mon, 16 Nov 2020 04:00:07 GMT

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?

Re: Unable to get value instead column name is printed

Tom — Mon, 16 Nov 2020 04:18:27 GMT

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));

Re: Unable to get value instead column name is printed

udupa_13 — Tue, 17 Nov 2020 05:08:04 GMT

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