How do I create a list of the last five 1 Aprils in PROC SQL?

Question

I want to create a table with a list of the last five 1 Aprils, i.e. if run today I would like the data to be

Snapshot
1APR2023
1APR2022
1APR2021
1APR2020
1APR2019

How can I do this?

I tried the following but it didn't work (gave me a syntax error):

/* Create an empty table */ 
proc sql;
   create table FinancialYearStarts (
      StartDate date
   );
quit;

/* Define the start date of the financial year */
%let FinancialYearStartDate = '01APR';

/* Use a DO loop to insert the last five starts of the financial year */
%macro PopulateFinancialYearStarts;
   %do i = 0 %to 4;
      proc sql;
         insert into FinancialYearStarts (StartDate)
         values (intnx('year', today(), -&i, 'sameday') + input(&FinancialYearStartDate., date9.));
      quit;
   %end;
%mend;

/* Execute the macro to populate the table */
%PopulateFinancialYearStarts;

Answer 1

I see that there's a small issue with the date format and macro code.

Updated code:

proc sql;
   create table FinancialYearStarts (
      StartDate date
   );
quit;

%let FinancialYearStartDate = '01APR';

%macro PopulateFinancialYearStarts;
   %do i = 0 %to 4;
      proc sql;
         insert into FinancialYearStarts (StartDate)
         values (intnx('year', today(), -&i, 'b') + input(&FinancialYearStartDate., date9.));
      quit;
   %end;
%mend;

%PopulateFinancialYearStarts;