topic Re: Global Macro Date Variable Passing in SAS Programming

Global Macro Date Variable Passing

BCNAV — Sat, 26 Oct 2019 20:47:40 GMT

I have set the following global macro var:

%let time_1mos_ago = %sysfunc(intnx(month,%sysfunc(today()),-1,end),YYMMDDS8.);

the %put of said variable is: TIME_1MOS_AGO=19/09/30

This is what I want. I would like to use time_1mos_ago elsewhere to choose dates.

I tried:

PROC SQL;
   CREATE TABLE EGTASK.QUERY_FOR_OVER_APPEND_0005_0000 AS 
   SELECT t1.billable_id, 
          t1.FltDate, 
          /* year */
            (year(&time_1mos_ago)) AS year
      FROM IBS_COMB.OVER_APPEND t1
      WHERE t1.FltDate <= &time_1mos_ago
      ORDER BY t1.FltDate;
QUIT;

This resulted in empty output....so I removed the restriction to see if the macro variable was passed through properly. I tested this via the above: year(&time_1mos_ago)) AS year

Year came back at 1960 when it should have been 2019. What is missing to ensure that the time_1mos_ago macro variable is passing the date properly so I can use it in many following steps?

Re: Global Macro Date Variable Passing

novinosrin — Sat, 26 Oct 2019 21:06:32 GMT

Hi @BCNAV

If you intend to use the date values as number to extract year from date value etc, you are better off not formatting it and just keeping the computed value as a number.

So You could use the below to make year function work

%let time_1mos_ago = %sysfunc(intnx(month,%sysfunc(today()),-1,end));

%put &=time_1mos_ago;

1    %let time_1mos_ago = %sysfunc(intnx(month,%sysfunc(today()),-1,end));
2
3    %put &=time_1mos_ago;
TIME_1MOS_AGO=21822

One may get confused %sysfunc with a format merely associates a format like a format statement in a datastep does, however in an open code that's not true, where everything is a text(i.e char) one should be mindful of the fact a result token alphanumeric is always a name token or special token and certainly not a number token at the time compilation done by the word scanner.

Re: Global Macro Date Variable Passing

novinosrin — Sat, 26 Oct 2019 21:20:10 GMT

To help further your understanding, your code

year(&time_1mos_ago)

basically resolves to

year(19/09/30)

which is 19 divided by 9 divided by 30 and that is equal to 0.0703703704 with integer value being 0. And if you format this, you would get a SAS date value 01jan1960. Taking the year('01jan1960') gives you 1960, which is erroneous. HTH