Generate-dynamic-picklist-of-months-and-years Part 2

[texasmfp]

This is a follow-up to this thread: Solved: Re: Generate dynamic picklist of months and years - SAS Support Communities

Again, thanks to Ksharp.  The following code generates a table of years and months for the 12 preceding months from a defined date:

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

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

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;

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

The table is:

year	month
2024	3
2024	4
2024	5
2024	6
2024	7
2024	8
2024	9
2024	10
2024	11
2024	12
2025	1
2025	2

I realized that I also need to put each of these year/month values into macro variables so that I can call them at various points later in the program.  Moreover, if the month value is 1-9 (i.e., January through September), it needs a 0 backfill in front of the value.  Ultimately, I need to dynamically generate something like this, again using the rmonth=02 and ryear=2025 starting date above:

%Let year1=2024;
%Let month1=03;
%Let year2=2024;
%Let month2=04;
%Let year3=2024;
%Let month3=05;
%Let year4=2024;
%Let month4=06;
%Let year5=2024;
%Let month5=07;
%Let year6=2024;
%Let month6=08;
%Let year7=2024;
%Let month7=09;
%Let year8=2024;
%Let month8=10;
%Let year9=2024;
%Let month9=11;
%Let year10=2024;
%Let month10=12;
%Let year11=2025;
%Let month11=01;
%Let year12=2025;
%Let month12=02;

 Thanks

[quickbluefish]

You could just do this:

data _null_;
set months;
n+1;
call symputx(compress("year" || n), year);
call symputx(compress("month" || n), put(month,z2.));
run;

[texasmfp · Accepted solution]

Thanks.  Actually, that did not work. 

2183  /*51*/
2184
2185  data _null_;
2186  set months;
2187  n+1;
2188  call symputx(compress("year" || n), year);
2189  call symputx(compress("month" || n), put(month,z2.));
2190  run;

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      2188:33   2189:34
ERROR: Symbolic variable name YEAR-1 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-1',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-1 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-1','12') at line 2189 column 6 is invalid.
date=202502 n=-1 year=2024 month=12 _ERROR_=1 _N_=3
ERROR: Symbolic variable name YEAR-2 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-2',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-2 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-2','11') at line 2189 column 6 is invalid.
date=202502 n=-2 year=2024 month=11 _ERROR_=1 _N_=4
ERROR: Symbolic variable name YEAR-3 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-3',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-3 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-3','10') at line 2189 column 6 is invalid.
date=202502 n=-3 year=2024 month=10 _ERROR_=1 _N_=5
ERROR: Symbolic variable name YEAR-4 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-4',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-4 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-4','09') at line 2189 column 6 is invalid.
date=202502 n=-4 year=2024 month=9 _ERROR_=1 _N_=6
ERROR: Symbolic variable name YEAR-5 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-5',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-5 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-5','08') at line 2189 column 6 is invalid.
date=202502 n=-5 year=2024 month=8 _ERROR_=1 _N_=7
ERROR: Symbolic variable name YEAR-6 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-6',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-6 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-6','07') at line 2189 column 6 is invalid.
date=202502 n=-6 year=2024 month=7 _ERROR_=1 _N_=8
ERROR: Symbolic variable name YEAR-7 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-7',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-7 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-7','06') at line 2189 column 6 is invalid.
date=202502 n=-7 year=2024 month=6 _ERROR_=1 _N_=9
ERROR: Symbolic variable name YEAR-8 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-8',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-8 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-8','05') at line 2189 column 6 is invalid.
date=202502 n=-8 year=2024 month=5 _ERROR_=1 _N_=10
ERROR: Symbolic variable name YEAR-9 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-9',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-9 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-9','04') at line 2189 column 6 is invalid.
date=202502 n=-9 year=2024 month=4 _ERROR_=1 _N_=11
ERROR: Symbolic variable name YEAR-10 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('year-10',2024) at line 2188 column 6 is invalid.
ERROR: Symbolic variable name MONTH-10 must contain only letters, digits, and underscores.
NOTE: Argument 1 to function SYMPUTX('month-10','03') at line 2189 column 6 is invalid.
date=202502 n=-10 year=2024 month=3 _ERROR_=1 _N_=12
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 12 observations read from the data set WORK.MONTHS.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

But this did:

proc sql ;
create table distinct_months as
select distinct year , put(month,z2.) as month
from months
order by year , month ;
quit ; 

proc sql noprint;
select year, month
into :year1 - :year12, :month1 - :month12
from distinct_months;
quit;

%put &=year1 &=month1; 
%put &=year2 &=month2; 
%put &=year3 &=month3; 
%put &=year4 &=month4; 
%put &=year5 &=month5; 
%put &=year6 &=month6; 
%put &=year7 &=month7; 
%put &=year8 &=month8; 
%put &=year9 &=month9; 
%put &=year10 &=month10;
%put &=year11 &=month11;
%put &=year12 &=month12;

[Tom]

The data step will work fine if you give it the same input, DISTINCT_MONTHS, as you gave the SQL query.

But you should not need to use the COMPRESS() function.  Just use the CATS() function.

data _null_;
  set distinct_months;
  n+1;
  call symputx(cats("year",n), year);
  call symputx(cats("month",n), month);
run;

If the goal is just to make the macro variables then skip making the dataset.

proc sql noprint;
select distinct year, put(month,z2.)
  into :year1- , :month1-
  from months
  order by year , month 
;
quit;

[Tom]

To get those error messages the dataset MONTH must have already had a variable named N.

If that is the case then the SUM statement will just increment the value of N read from the source dataset by one.  So an existing value of -2 would be transformed to -1 and yield a variable name of YEAR-1.

Run this example to see:

data good(drop=n) bad;
  do n=-12 to 0 ;
     date = intnx('month',today(),n);
     year=year(date);
     month=month(date);
     output;
  end;
run;

data _null_;
  set good;
  n+1;
  call symputx(cats('year',n),year);
run;

data _null_;
  set bad;
  n+1;
  call symputx(cats('year',n),year);
run;

[texasmfp]

Yes, the solution (from the other thread) created a months dataset with 4 variables, including n.

Thanks