Hello SAS Community,
I've been working on a project that queries data each quarter and I wanted to create a macro that will make a column with the current federal quarter and fiscal year column. Essentially this:
So I started by making macros. At the time of creating them, they were resolving and the columns were created in my dataset. However, I was puzzled to find that I received this warning/error from SAS when revisiting my program this morning:
Here is the code I used to create the macros:
/* Create quarter and fiscal year macro variable.*/ %macro GetQuarterFromDate();; %let date = %sysfunc(today()); /* Get today's date */ %let quarter = 0; %let month = %sysfunc(month(&date)); %if &month <= 3 %then %let quarter = 3; %else %if &month <= 6 %then %let quarter = 4; %else %if &month <= 9 %then %let quarter = 1; %else %if &month <= 12 %then %let quarter = 2; %put Quarter &quarter; %mend; %GetQuarterFromDate(); %macro GetFiscalYearFromDate(); %let date = %sysfunc(today()); /* Get today's date */ %let startYear = %eval(%sysfunc(year(&date)) - (%sysfunc(month(&date)) <= 6)); %let endYear = %eval(&startYear + 1); %let fiscalYear = FY &startYear.-&endYear.; %put &fiscalYear; %mend; %GetFiscalYearFromDate();
Here is the code I used when creating a column with the macro as its value in a dataset. I swapped out my original dataset to SASHELP.stocks, if you want to test:
data quar_fisc; set sashelp.stocks; %GetQuarterFromDate; QuarterColumn = catx(' ', "Quarter", &quarter); /* Concatenate text with quarter value */ %GetFiscalYearFromDate; FiscalYearColumn = "&fiscalYear"; run; data final; set quar_fisc; CombinedColumn = catx(', ', QuarterColumn, FiscalYearColumn); drop QuarterColumn FiscalYearColumn; run;
Any help is much appreciated!
You seem still a little confused about how macro processing works. It makes no sense to call those %GetQuarterFromDate and %GetFiscalYearFromDate macros in the middle a data step. Since the only thing they generate is macro variable values they will completed processes before the data step start running any way. You should call them before you start writing the data step. Now the order of the lines in the program file will more closely match the order that they will be executed.
And if you want the value of QUARTER and FISCALYEAR to be available after the macros have finished running you need to have created those macro variables before the macros are called. Otherwise the default action of making unknown new macro variables as LOCAL will apply and they will disappear when the macro call ends.
%let quarter=; %let fiscalyear=; %GetQuarterFromDate; %GetFiscalYearFromDate; data quar_fisc; set sashelp.stocks; QuarterColumn = catx(' ', "Quarter", &quarter); /* Concatenate text with quarter value */ FiscalYearColumn = "&fiscalYear"; run;
Reeza answered your main question. As an aside, to calculate the quarter instead of using a macro, you could use a single macro expression, like:
%put The quarter for today is: %sysfunc(intnx(quarter,%sysfunc(today()),2),qtr) ;
That is basically "calculate the quarter of the quarter that is two quarters ahead of today."
In your DATA step, you could have the assignment statement be:
QuarterColumn = "Quarter %sysfunc(intnx(quarter,%sysfunc(today()),2),qtr)" ;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.