select variable automatically by using macro

[dht115]

Hello, 

• Say you read in a dataset with weeks as column names, WEEK1 to WEEK6 and they provide a corresponding average for a certain material.
• You then make a calculation based on the data in each of those columns, in my case, it’s an average of the past three weeks.
• However, every month I run this code, those columns are going to change, for instance, I would need WEEK4, WEEK5, WEEK6 on week 7.
• Is it possible for us to write a macro variable to select these variable automatically? 

/*SAMPLE DATA SET*/

data have;
   input mat $ week1 week2 week3 week4 week5 week6;
datalines;
abc 2 3 4 5 6 7
bcd 4 5 6 1 7 
cde 10 18 0 11 12 13
;
run;

/*WHAT I CAN DO*/
/*to change %let first value manually to select future 3 week average*/

data want;
Set have;
/*I need to change value for first variable manually, i.e.(1, 4, 7, 11) to select future weeks*/
%let first = 1;
%let second = %eval(&first. + 1);
%let third = %eval(&second. + 1);

DMD_Q_AVG_dt=mean(of week&first.-week&third.);

run;

[Rick_SAS]

How are your weeks numbered? Are these "weeks since the study began" or "weeks of the year"?

If these represent weeks of the year, you can use the WEEK function to obtain the current week from the TODAY function. You can then use the SYMPUTX call to create the macro variables:

data _null_;
curWeek = week( today(), 'u');
put curWeek;
call symputx("first", curWeek);
call symputx("third", curWeek+2);   /* Caution: might not work at end of year */
run;

%put &=first;
%put &=third;

You might consider an alternative data format (the "long format") where each week is indicated by a value in a variable:

data _null_;
curWeek = week( today(), 'u');
put curWeek;
call symputx("first", curWeek);
call symputx("third", curWeek+2);
run;

%put &=first;
%put &=third;

data Have;
input Week Value;
datalines;
1 1.2
1 2.3
1 3.4
2 2.2
2 2.3
2 4.1
3 1.5
3 2.4
3 3.3
;

With the long format, you can use a WHERE clause to obtain the information you want without needing to manufacture variable names. Also, if the number of subjects in your study changes over time, the long format can accommodate that change. For example, you might have 10 measurements for the first week but only 9 measurements for the second week.

[PaigeMiller]

Agreeing with @Rick_SAS , that the long format makes coding easier in the long run.

In your example data, do you really have week1 - week52? Or if the data spans more than one year, would there be a week53 and week54 and so on? Again, long format would be much easier to handle.

In your example, it's not clear why macros are needed at all, ARRAYs can handle this. If you know what month you are in, you can also determine which weeks you want.