If you are the one who is creating these monthly data sets then be nice to your data consumers and create views for them for the most often required windows like "current month", "current quarter", "last quarter" and so on - and update (re-create) these views whenever you create a new monthly data set.  If you are one of the data consumers then I'd request such views as that's really not hard to code and implement.   Below some code which should give you what you have been asking for. data sales201512 sales201601 sales201602; product_id=1; output; product_id=2; output; run; %macro create_view ( view_name=, base_name=, start_yyyymm=, n_months= ); proc sql; create view &view_name as %do i=0 %to %eval(&n_months-1); select * from &base_name%sysfunc(intnx(month, %sysfunc(inputn(&start_yyyymm, yymmn6.)), &i), yymmn6.) %if &i ne %eval(&n_months-1) %then %do; union all %end; %end; ; quit; %mend; %create_view ( view_name=v_3months, base_name=sales, start_yyyymm=201312, n_months=3 ); data test; set v_3months; where product_id=1; run; proc print data=v_3months; where product_id=2; run; ... View more

The most efficient way is ARRAY. But I am too lazy, so use SQL instead of . data have; input (Customer PA1 PA2 PA3) ($:); datalines; A H M L A H L M A H M H ; run; proc sql; create table temp as select distinct Customer,'PA1' length=10 as id,PA1 as value   from have union all select distinct Customer,'PA2' length=10 as id,PA2 as value   from have union all select distinct Customer,'PA3' length=10 as id,PA3 as value   from have; quit; data temp1; set temp; by Customer id; length v $ 200; retain v; v=catx(' & ',v,value); if last.id then do;output;call missing(v);end; drop value; run; proc transpose data=temp1 out=want(drop=_:); by Customer; id id; var v; run; ... View more

Not sure why you need so many macro variables. Personally I do not like the idea of macro "arrays" as I find that most programs are easier to maintain if you use a different coding pattern. The problem with using a macro to generate macro variables that you want to use later is that by default the macro variables are defined in the scope of the sub-macro and so disappear when it ends.  One way to deal with this is to force the macros to be made in the GLOBAL macro scope as others have posted above. This can cause trouble with other parts of your code that might want to use those same macro variable names for some other meaning.  Here are two methods to allow you define the new macros in the current macro scope (not the scope of the sub macro and not necessarily the GLOBAL scope either). 1) Is to take advantage of a feature of the fact that if a sub macro does not have any macro variables at all then it will not generate a new macro scope.  Note that this means that the macro cannot take any parameters either.  So your number of months will have to placed into another macro variable first before calling the new macro. %macro lookback; data _null_;   do i=1 to input(symget('months'),comma32.);      call symputx(cats('date_',i),putn(intnx('month',today(),-1*i,'e'),'yymmddn8.'));   end; run; %mend lookback ; %let months=6; %lookback; 2) Another method is to have the macro return the text of the %LET statements (without actually running) them as quoted macro text. And then use %UNQUOTE() to execute the %LET statements in the calling environment.  This will let you define the macro with parameters and local macro variables. %macro lookback(months,start=); %local i str; %if ^%length(&start) %then %let start=%sysfunc(today()); %do i=1 %to &months ;   %let str=&str.%nrstr(%let )date_&i=%sysfunc(intnx(month,&start,-&i,e),yymmddn8.)%nrstr(;); %end; &str. %mend; %unquote(%lookback(6)); ... View more