How to calculate a fiscal month given a specific date using a calculated column

[TelecomSasUser]

In SAS Enterprise Guide, I'm trying to create a calculated column that converts a date (in the DATETIME26.6 format) to a date format fiscal month.

If the day is the 1st through the 21st of the month, I want the fiscal month to be the first day of that month.

If the day is the 22nd through the 31st of the month, I want the fiscal month to be the first day of THE NEXT month

As an example, Jan 21st 2019 should return Jan 1st 2019

And Jan 22nd 2019 should return Feb 1st 2019

I've written the code and tested both the INTNX and comparison in pieces (and they've worked), but when I include it in a case statement, my returns are all coming out as '."

Or analogously, if there's a better way of doing this, I'm happy to adopt a new solution altogether!

CASE
	WHEN DAY(DATEPART(t3.CRE_DT))<22
		THEN 	
			INTNX(	'DAY', 
				t3.CRE_DT, 
				1-DAY(DATEPART(t3.CRE_DT))
			)
	WHEN DAY(DATEPART(t3.CRE_DT))>=22
		THEN
			INTNX(	'MONTH', 
				INTNX(	'DAY', DATEPART(t3.CRE_DT), 1-DAY(DATEPART(t3.CRE_DT)), 
				1
			)
END

[ballardw · Accepted solution]

No need for a CASE statement at all. Consider IFN function in below.

data example;
   do CRE_DT='01JAN2019:12:15:00'dt to '31Jan2019:12:15:00'dt by 3600;
      newdate = ifn(day(datepart(CRE_DT))<22,datepart(intnx('dtmonth',CRE_DT,0,'B')),datepart(intnx('dtmonth',CRE_DT,1,'B')),.);
      output;
   end;
   format CRE_DT datetime19.  newdate date9.;
run;

Since your t3.CRE_DT variable is a DATETIME the proper interval would be 'DTDAY' or 'DTMONTH'

[TelecomSasUser]

Thanks so much! This worked like a charm (and a much cleaner solution than my original attempt)

[ballardw]

---

@TelecomSasUser wrote:

Thanks so much! This worked like a charm (and a much cleaner solution than my original attempt)

---

I'm not generally a fan of the IFN and IFC functions as they can lead to some longish hard to read code (like this) but I have even more of an issue with CASE statements.