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?
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));
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.