Date Calculation

[mauri0623]

I need to compute invoice date based on the following logic,

Logic: 28th day of the prior month as compared to the "Invoice Date" (i.e. if invoice date = 20191213 then date = 20191128. This is fine until you get to the Jan month and gets tricky.

[Tom]

It is easy to calculate using INTNX() function.  That will let you move a date by any of many intervals, and pick which date in that interval to generate (beginning, end or same).  To get to the 28th move to the 1st and add 27.

date = intnx('month',invoice_date,-1,'b')+27;

[ed_sas_member · Accepted solution]

Hi @mauri0623 

As mentioned by @Tom, the INTNX() is the function you need.

You can try this:

data want;
	Invoice_date = "20190114";
	date = compress(put(intnx("day",(intnx("month",input(Invoice_date,YYMMDD10.),-1)),27),YYMMDD10.),"-");
run;

Several functions are nested:

- The intnx("month",input(Invoice_date,YYMMDD10.),-1) part retrieves the first day of the last month

- then the intnx("day",<previous part>, 27) adds 27 days and so retrieves the 28th of the previous month

- the put and the compress function enable to retrieve the date in character in a human readable way (e.g. 20191128). You can remove this step if you want to keep a date.

[novinosrin]

Hi @ed_sas_member   Appreciate your details but it can be much more concise with the use of format rather than compress

YYMMDDn8.

put(intnx("day",(intnx("month",input(Invoice_date,YYMMDD10.),-1)),27),YYMMDDn8. -l);

- l --> just left aligning the result

[ballardw]

---

@mauri0623 wrote:

I need to compute invoice date based on the following logic,

Logic: 28th day of the prior month as compared to the "Invoice Date" (i.e. if invoice date = 20191213 then date = 20191128. This is fine until you get to the Jan month and gets tricky.

---

Is your 20191213 a SAS date value, numeric with a format of YYMMDDN8. ? If that value is a plain number then you really need to convert it to a SAS date value for manipulation.