Macro help for dates

[Justin9]

Hi,

I currently have an extract of code (shown below), but I would like to make the dates macros and create a new program specifically to enter the date, which will save time for the user instead of having to scroll through the lines of code and updating the dates.

Current code:

proc sql;
      create table date_analysis as
             select
                   a.*
                  ,b.*
                  ,c.*
      from (select * from dataset_alpha_2021 where month_end=22584) a
             inner join (select * from dataset_beta_2021 where month_end='31OCT2021'd) b
                      on a.account_num=b.account_num
   left join dataset_gamma_Oct21 c
    on a.account_num=c.account_num
      ;
quit;

Proposed changes to have macros:

I will create a new program for the user to set the date (this will be manually updated for the month-end e.g. 31Oct2021 (for October 2021), 30Nov2021 (for November 2021) etc.. where I would like four macros to be created:

1. The best starting point might be to create a macro called month_end 

2. yyyy (for the two scenarios where 2021 is used)

3. run_date. In dataset_alpha_2021, the month_end variable only lists numeric date value e.g. 22584, which I had to calculate as being equal to 31 Oct 21. Could this line be edited so that the format of month_end is equal to the date macro in the dataset_beta_2021 line of code?

4. monyy

%let month_end=31Oct2021; /*The date will be manually updated by the user e.g. 30Nov2021, 31Dec2021 etc. The three macros below should automatically calculate based on the date input for this month_end macro*/
           /*(used in the dataset_alpha_2021 line of code)*/

%let run_date=[not sure how to have date value e.g. 22584 equalling month_end macro above?]  
           /*(used in the dataset_beta_2021 line of code)*/

%let yyyy=[something like %sysfunc(intnx(month,"month_end"d,0,e),monyy5.); ? 
          /*(used in the dataset_alpha_2021 and dataset_beta_2021 lines of code)*/

&let monyy=[something like %sysfunc(year("month_end"d));  ? 
          /*(used in the dataset_gamma_2021 line of code)*/

After the macros have been applied, it could look something like this (note that I am not sure what the best way is to have the macro for the date value (maybe call it something like "run_date") being equal to the month_end macro (i.e. since month_end has been set as "31Oct2021", the run_date macro would automatically pick up the 22584 values from the dataset_beta_2021 dataset as they are equal to the same date).

proc sql;
      create table date_analysis as
             select
                   a.*
                  ,b.*
                  ,c.*
      from (select * from dataset_alpha_&yyyy. where [not sure which format(month_end)=["&run_date"d i.e. the number 22584 is equal to 31Oct2021]) a
             inner join (select * from dataset_beta_&yyyy. where month_end="&month_end"d) b
                      on a.account_num=b.account_num
             left join dataset_gamma_&monyy. c
                      on a.account_num=c.account_num
      ;
quit;

Thanks in advance.

[PaigeMiller]

Let me first say I'm not totally sure what you want. I have read your description of run_date and I'm lost. I don't know what you want.

Nevertheless, the advice is to leave macro variable dates UNformatted*. Let me say that again. Leave macro variable dates UNformatted*.

So ...

%let month_end=%sysevalf('31Oct2021'd);
%put &=month_end;

produces a value of 22584, and that is exactly what you want. All arithmetic and logical operations can be performed properly on a macro variable whose value is 22584. Why? Because SAS performs all arithmetic and logical operations on the unformatted values; so converting it to something human readable such as 21OCT2021 is wasted effort, and then you have to unformat it so that SAS can use it. The same would apply to &run_date, leave it UNformatted*.

Then you talk about this:

%let yyyy=[something like %sysfunc(intnx(month,"month_end"d,0,e),monyy5.); ? 

DO NOT FORMAT THIS MACRO VARIABLE*

What is that asterisk? The asterisk indicates that the only time you would actually go through the trouble of formatting your macro variable date values is when you need them in a title, or a label, or a file name. And that's it.

[Justin9]

Thanks for your suggestion. My first dataset, dataset_alpha_2021, includes all values that are numeric and are in the date value format e.g. 22584. This would take time for the user to have to manually update and calculate every month-end, so I would like to create a "run_date" macro that would automatically pick up the same date that is listed in the first "month_end" macro.

As a result, the "month_end" macro would be the only macro that the user has to manually update (e.g. %let month_end=31Oct2021 or %let month_end=31Oct21, depending on which is easier to base the next three macros on). The next three macros would automatically calculate based on the date written in the "month_end" macro:

%let month_end=31Oct2021; 
        /*Only macro to be manually updated*/

%let run_date=    ;
        /*This macro should automatically pick the date from the month_end macro, but the difficulty is that all of the values in the dataset are numeric date values e.g. 22584, so I would like this macro to equate to the month_end macro date - this could potentially mean changing the line of code if I need to format the line to do this (the line in the code is: "from (select * from dataset_alpha_2021 where month_end=22584) a)"*/

%let yyyy=    ;
      /*This macro should automatically pick the date from the month_end macro to get the year i.e. 2021, based on the example written in the month_end macro above*/

%let monyy=   ;
    /*This macro should automatically pick the date from the month_end macro to get the monyy i.e. Oct21, based on the example written in the month_end macro above*/

Please can you suggest the best way for me to set up the four macros?

[Kurt_Bremser]

Just as in data step code, use the INPUTN function for the conversion:

%let month_end=31Oct2021; 
%let run_date=%sysfunc(inputn(&month_end.,date9.));
%put &=run_date.;

[PaigeMiller]

So @Kurt_Bremser provides the correct answer.

I would like to point out, for clarity, that you talk about macros in many places, but you really mean macro variables. Macros are not macro variables. Macro variables are not macros. The two terms "macros" and "macro variables" should not be used interchangeably.

[Quentin]

Hi,

I think you're close to what you want.  You have the right idea that users should only specify a single date value as input, and the code should derive the rest of the values from that input date.

I think it's easiest to have users specify a date literal.  They are writing SAS code, so they should know how to do it, and with a date literal SAS knows it's a date.  If the input value is a date literal, you can use all the functions you mention (INTNX, YEAR, PUTN) to calculate values from that input.

As an example:

1    %let date="01Feb2022"d;
2
3    %put the date is: &date ;
the date is: "01Feb2022"d
4    %put the SAS date numeric value is: %sysfunc(putn(&date,8 -l)) ;
the SAS date numeric value is: 22677
5    %put the month end is: "%sysfunc(intnx(month,&date,0,e),date9)"d ;
the month end is: "28FEB2022"d
6    %put the year is: %sysfunc(year(&date)) ;
the year is: 2022
7    %put the monyy is: %sysfunc(putn(&date,monyy5)) ;
the monyy is: FEB22

HTH,

--Q.

[Kurt_Bremser]

Adding to what @PaigeMiller already said, it is important to know how the dates will be supplied. If you use User Prompts in Enterprise Guide or a stored process, those will supply dates in the DATE9 format. If you use other means, I suggest you add an additional %LET for each variable at the beginning your code that converts the agreed-upon human-readable format to the raw value.

[Justin9]

Thanks for your message.

The first dataset, "dataset_alpha_2021", has a "month_end" variable that is a numeric date value. For example, the values are 22584. This is the difficulty I am having (the user might not know that 22584 is 31Oct2021, which will be an issue at the end of every month e.g. for 30Nov2021, they might not know the date value), as I would like to create a macro called "run_date" which automatically picks out the date that I have manually set in the first "month_end" macro.

The second dataset, "dataset_beta_2021", has a month_end variable that is a numeric type (and date for the group section). For example, the values are JAN2021, FEB2021 etc. The "month_end" macro is the one that the user would update every month e.g. "%let month_end=30Nov2021" (or "%let month_end=30Nov21" if that's easier).

The third dataset, "dataset_gamma_Oct21" is the one where I would like to use a macro called "monyy", so that the month and year get automatically picked up from the date set in the first "month_end" macro. This is so that if I set the first macro as "%let month_end=30Nov2021" (or "%let month_end=30Nov21" if that's easier), then the "dataset_gamma_&monyy." would automatically turn to "dataset_gamma_Nov21".

The fourth macro, "yyyy", would calculate based on the date input in the "month_end" macro. For example, if "%let month_end=31Jan2021" (or "%let month_end=31Jan22" if that's easier), then "dataset_alpha_&yyyy." in the code would automatically turn to "dataset_alpha_2022".

[PaigeMiller]

---

@Justin9 wrote:

The first dataset, "dataset_alpha_2021", has a "month_end" variable that is a numeric date value. For example, the values are 22584. For example, the values are 22584. This is the difficulty I am having (the user might not know that 22584 is 31Oct2021, which will be an issue at the end of every month e.g. for 30Nov2021, they might not know the date value)

---

The user does not have to know the date is 22584. The user has to know 21OCT2021 or whatever the date is. They specify 21OCT2021, and the code does the rest. The code provided by @Kurt_Bremser translates 21OCT2021 into something that SAS understands, which is 22584. So the user speaks in language that (s)he understands, and the code then converts it to something SAS understands. Later, when there is some output (table or report), the date 22584 in SAS is translated back to the language the user understands, which is done via formatting.

[Tom]

You seem a little confused about how SAS handles dates. Let's clear that up before getting to how the MACRO language can be used to help generate your code.

The value SAS stores for date is the number of days since 1960.  So the number 22,584 in your first WHERE condition is the exact same number as the date literal '31OCT2021'd in your second WHERE condition.

312   %put %sysevalf('31OCT2021'd);
22584

Now as to how to use the MACRO language to help generate your code you need to create three things.

1) A actual date value to use in BOTH where clauses.

2) A four digit string to use in two of the dataset names.

3) A five character string to use in the other dataset name.

If you want to be really friendly to your users allow them to type in anything that the ANYDTDTE informat will recognize as a date value.

Use INPUTN() to convert the user string into a date value. Then use INTNX() to convert it to the END of the month.

Use YEAR() to convert the date value into a year digit string. Use PUTN() with MONYY5. format to create the MONYY string. (Why are you using only two digits for the year here?).

%let month_end=%sysfunc(inputn(&user_input,anydtdte24.));
%let month_end=%sysfunc(intnx(month,&month_end,0,e));
%let year=%sysfunc(year(&month_end));
%let monyy=%sysfunc(putn(&month_end,monyy5.));
%put &=user_input &=month_end &=year &=monyy;

proc sql;
create table date_analysis as
select
     a.*
    ,b.*
    ,c.*
from (select * from dataset_alpha_&year. where month_end=&month_end.) a
inner join (select * from dataset_beta_&year. where month_end=&month_end.) b
  on a.account_num=b.account_num   
left join dataset_gamma_&monyy. c    
on a.account_num=c.account_num
;
quit;

So to replicate your original code the user_input macro variable could any value that the ANYDTDTE informat will convert to date in October of 2021.  Values like:

OCT 2021
1oct21
2021/10/20