Generate dynamic picklist of months and years

texasmfp — Tue, 03 Feb 2026 04:21:58 GMT

I have historical monthly data that is organized under folder names that start with a year and a month. I have a short program that creates a pick list consisting of a year and a month. This data will be used later in a macro to select the datafile within each of the respective unique folder names.

%let rmonth = 02;
%let ryear = 2025;
* Assure that RMONTH is zero-filled (eg, 03 not 3) ;
%let rmonth = %sysfunc(putn(&rmonth,z2.) ) ;

* Create list of months for which data are needed. ;
data months ;
Year = &ryear     ; Month = &rmonth ; output ;
year = &ryear - 1 ; month = &rmonth ; output ;
year = &ryear - 1 ; month =      12 ; output ;
year = &ryear - 2 ; month =      12 ; output ;
year = &ryear - 3 ; month =      12 ; output ;
year = &ryear - 4 ; month =      12 ; output ;
year = &ryear - 5 ; month =      12 ; output ;
year = &ryear - 6 ; month =      12 ; output ;
run ;

proc sql ;
create table distinct_months as
 select distinct year , month
  from months
 order by year , month ;
quit ;

This works just fine where I am selecting the month for a prior year, and the month of December for 6 prior years.

However, I want to create a new report, one that selects monthly data from each of the prior 12 months sequentially. The following revision works fine, but only if the month is December. In the other months, to get the prior 12, one needs to -1 (i.e., subtract one from the year) for one or more of the lines and then add 1, 2, 3, etc... to the month, as appropriate, to generate the correct picklist.

data months ;
Year = &ryear  ; month = &rmonth ; output ;
year = &ryear  ; month = &rmonth -1 ; output ;
year = &ryear  ; month = &rmonth -2 ; output ;
year = &ryear  ; month = &rmonth -3 ; output ;
year = &ryear  ; month = &rmonth -4 ; output ;
year = &ryear  ; month = &rmonth -5 ; output ;
year = &ryear  ; month = &rmonth -6 ; output ;
year = &ryear  ; month = &rmonth -7 ; output ;
year = &ryear  ; month = &rmonth -8 ; output ;
year = &ryear  ; month = &rmonth -9 ; output ;
year = &ryear  ; month = &rmonth -10 ; output ;
year = &ryear  ; month = &rmonth -11 ; output ;
run ;

I suppose that I could hardcode 12 iterations, one for each starting month and then writing code that tells SAS to use the appropriate iteration based on the starting month. I am hoping there is a more sophisticated way to make this dynamic and avoid 12 sets of instructions. Thanks

Re: Generate dynamic picklist of months and years

Ksharp — Tue, 03 Feb 2026 07:23:37 GMT

Do you want to pick up the previous 12 months before a date ?

%let rmonth = 02;
%let ryear = 2025;

data months ;
date=input("&ryear.&rmonth.",yymmn6.);
format date yymmn6.;
do n=0 to -11 by -1;
  year=year(intnx('month',date,n));
  month=month(intnx('month',date,n));
  output;
end;
run;

Re: Generate dynamic picklist of months and years

texasmfp — Tue, 03 Feb 2026 08:09:15 GMT

Beautiful concept in its simplicity! Bring them together, generate, separate! Thanks

topic Generate dynamic picklist of months and years in SAS Programming

Generate dynamic picklist of months and years

Re: Generate dynamic picklist of months and years

Re: Generate dynamic picklist of months and years