topic Re: How do I can write macro program with date in SAS Programming

How do I can write macro program with date

Ccasagran737 — Thu, 22 Nov 2018 09:33:58 GMT

Hi, I am trying to assign different value to macro "dt_rif_short_Ifrs9" depending on the "dt_riferimento" macro value but the macro program seems doesn't work correctly. (for example if &dt_riferimento = "31MAR2018"d, &dt_rif_short_Ifrs9 = 201709 and not 31MAR2018 as I want).

Do you know why? I think there is some problems with date value...let me know.

I am using SAS University Edition.

Thanks for your time and help.

Daniele

%let dt_riferimento = "31MAR2018"d;

macro ifrs9 ();

%global dt_rif_short_Ifrs9;
%if "31DEC2017"d <= &dt_riferimento. < "31MAR2018"d %then %let dt_rif_short_Ifrs9 = 201709;
%else %if "31MAR2018"d <= &dt_riferimento. < "30JUN2018"d %then %let dt_rif_short_Ifrs9 = 201803;
%else %if "30JUN2018"d <= &dt_riferimento. < "30SEP2018"d %then %let dt_rif_short_Ifrs9 = 201806;
%else %if "30SEP2018"d <= &dt_riferimento. < "31DEC2018"d %then %let dt_rif_short_Ifrs9 = 201809;
%else %let dt_rif_short_Ifrs9= "error";

run;
%mend;
%ifrs9 ();

%put the value of the macro variable dt_rif_short_Ifrs9 is: &dt_rif_short_Ifrs9.;

Re: How do I can write macro program with date

RW9 — Thu, 22 Nov 2018 09:50:09 GMT

Macro is a text based find and replace system. As it is text, all text is evaluated as text. Dates and such like do not exist in macro. You can force a numeric comparison by use of the %eval() function, however I really think the problem here is the misuse of macro language. It is Not a replace for base sas coding. It is a separate text generation tool for a specific purpose, not to re implement Base SAS.

Tells us what your trying to do, what inputs and outputs you have (i.e. what you have, and what you want). You will want to do your logic and data processing in datastep, e.g:

%let dt_riferimento=31MAR2018;

data want;
  set have;
  if "31DEC2017"d <= "&dt_riferimento."d < "31MAR2018"d then...
...
run;

Re: How do I can write macro program with date

Kurt_Bremser — Thu, 22 Nov 2018 09:55:57 GMT

The macro preprocessor is not good for handling data, as it is meant to only create program text.

Use the proper tool, which is a data step:

data _null_;
if "31DEC2017"d <= &dt_riferimento. < "31MAR2018"d then dt_rif_short_Ifrs9 = "201709";
else if "31MAR2018"d <= &dt_riferimento. < "30JUN2018"d then dt_rif_short_Ifrs9 = "201803";
else if "30JUN2018"d <= &dt_riferimento. < "30SEP2018"d then dt_rif_short_Ifrs9 = "201806";
else if "30SEP2018"d <= &dt_riferimento. < "31DEC2018"d then dt_rif_short_Ifrs9 = "201809";
else dt_rif_short_Ifrs9 = 'error';
call symputx('dt_rif_short_Ifrs9',dt_rif_short_Ifrs9,'g');
run;

%put &dt_rif_short_Ifrs9;

Log excerpt:

36         %put &dt_rif_short_Ifrs9;
201803

Shouldn't the value for the first condition be "201712"? If that were true, you can replace the whole if-then-else-if avalanche with a formula.

Re: How do I can write macro program with date

DanielLangley — Thu, 22 Nov 2018 13:01:18 GMT

I agree completely with @Kurt_Bremser. This could be the set of formulas you use.

/*************** TEST DATA ***************/

data tests;
	attrib TestDate format = date9.;
	do TestDate = "15Dec2017"d to "15Dec2018"d by 14;
		output;
	end;
run;

/*** Demonstrates how the process works using test data ***/
data testingdates;
	set tests;
	attrib wantDate firstDayOfWantDate format = date9.;
	/*find month and year*/
	dateMonth = month(TestDate);
	dateYear = year(TestDate);
	/*modify month to find the group month*/
	outputMonth = 2 + dateMonth - mod(dateMonth - 1,3);
	/*create a date using the year and modified date*/
	firstDayOfWantDate = mdy(OutputMonth,1,dateYear);
	/*find the date of the end of the month of the created date*/
	wantDate = intnx('month',firstDayOfWantDate,0,'end');
run;

/*************** WANT ***************/

%let dt_riferimento=12JAN2016;

data _null_;
TestDate = "&dt_riferimento"d;

	/*find month and year*/
	dateMonth = month(TestDate);
	dateYear = year(TestDate);
	/*modify month to find the group month*/
	outputMonth = 2 + dateMonth - mod(dateMonth - 1,3);
	/*create a date using the year and modified date*/
	firstDayOfWantDate = mdy(OutputMonth,1,dateYear);
	/*find the date of the end of the month of the created date*/
	wantDate = intnx('month',firstDayOfWantDate,0,'end');
	/*format the date how you want*/
	FormatWantDate = coalescec( put(wantDate,date9.) ,'error');

CALL SYMPUTX('dt_rif_short_Ifrs9',FormatWantDate,'G');
run;

%PUT dt_rif_short_Ifrs9 = &dt_rif_short_Ifrs9;

It includes some examples and the method that produces what you want.

If it specifically needs to be between those particular dates you could then use a formula at the end to turn it into the error value you want.

You might also want to look into using custom formats to do this.

Re: How do I can write macro program with date

Reeza — Thu, 22 Nov 2018 15:51:26 GMT

This type of logic works in a data step but does not work in the macro language

 %if "31DEC2017"d   <= &dt_riferimento. < "31MAR2018"d

You'll also likely have to use %EVAL or %SYSEVALF to get it to be correct.

It's probably easier to do this in a data _null_ step rather than a macro.

@Ccasagran737 wrote:

Hi, I am trying to assign different value to macro "dt_rif_short_Ifrs9" depending on the "dt_riferimento" macro value but the macro program seems doesn't work correctly. (for example if &dt_riferimento = "31MAR2018"d, &dt_rif_short_Ifrs9 = 201709 and not 31MAR2018 as I want).

Do you know why? I think there is some problems with date value...let me know.

I am using SAS University Edition.

Thanks for your time and help.

Daniele

%let dt_riferimento = "31MAR2018"d;

macro ifrs9 ();

%global dt_rif_short_Ifrs9;
%if "31DEC2017"d <= &dt_riferimento. < "31MAR2018"d %then %let dt_rif_short_Ifrs9 = 201709;
%else %if "31MAR2018"d <= &dt_riferimento. < "30JUN2018"d %then %let dt_rif_short_Ifrs9 = 201803;
%else %if "30JUN2018"d <= &dt_riferimento. < "30SEP2018"d %then %let dt_rif_short_Ifrs9 = 201806;
%else %if "30SEP2018"d <= &dt_riferimento. < "31DEC2018"d %then %let dt_rif_short_Ifrs9 = 201809;
%else %let dt_rif_short_Ifrs9= "error";

run;
%mend;
%ifrs9 ();

%put the value of the macro variable dt_rif_short_Ifrs9 is: &dt_rif_short_Ifrs9.;

Re: How do I can write macro program with date

s_lassen — Fri, 23 Nov 2018 11:22:12 GMT

To get the macro to evaluate the dates correctly, you should use %SYSEVALF.

And then there is the other problem: SAS Macro language does not understand a<=b<c the same way as the datastep. Instead of translating it to "a<=b and b<c" it resolves it to "(a<=b)<c". And (a<=b) always resolves to 1 (true) or 0 (false). And if c is a recent date value, the total comparison always comes out as 1 (true).

And then I think you should avoid declaring %GLOBAL macro variables inside a macro, if you can. And in this case, you can. Just rewrite the macro as a macro function, like this:

%macro ifrs9 (dt);
    %if %sysevalf(&dt < "31DEC2017"d) %then %do;error%end; 
    %else %if %sysevalf(&dt < "31MAR2018"d)   %then %do;201709%end;
    %else %if %sysevalf(&dt <  "30JUN2018"d) %then %do;201803%end;
    %else %if %sysevalf(&dt <  "30SEP2018"d) %then %do;201806%end;
    %else %if %sysevalf(&dt <  "31DEC2018"d) %then %do;201809%end;
    %else %do;error%end;
%mend;

I put all the %do...%end stuff in to avoid spurious blanks and hanging semicolons.

So you use the macro like this:

%let dt_riferimento = "31MAR2018"d;
%let dt_rif_short_Ifrs9=%ifrs9(&dt_riferimento);
%put the value of the macro variable dt_rif_short_Ifrs9 is: &dt_rif_short_Ifrs9.;

Or you can just use it like this:

%put The IFRS9 value is %ifrs9(&dt_riferimento);

(If you are not referring to your calculated macro variable a lot of times, you can just put the macro call in where you need the value)

Re: How do I can write macro program with date

Ccasagran737 — Fri, 23 Nov 2018 13:45:47 GMT

Thanks I have just tried your solution and it works. Great!

ps yes, the value of the first condition is supposed to be "201712".

Re: How do I can write macro program with date

Kurt_Bremser — Fri, 23 Nov 2018 14:03:34 GMT

@Ccasagran737 wrote:

ps yes, the value of the first condition is supposed to be "201712".

In which case you should take a closer look at @DanielLangley's suggestion. I would work along the same lines of thinking.

Re: How do I can write macro program with date

Ccasagran737 — Sat, 24 Nov 2018 06:10:49 GMT

Thanks....I have found your advice very intresting.