topic Date Calculation in SAS Programming

Date Calculation

mauri0623 — Mon, 16 Dec 2019 15:52:12 GMT

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.

Re: Date Calculation

Tom — Mon, 16 Dec 2019 16:03:20 GMT

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;

Re: Date Calculation

ed_sas_member — Mon, 16 Dec 2019 16:13:19 GMT

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.

Re: Date Calculation

ballardw — Mon, 16 Dec 2019 16:52:42 GMT

@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.

Re: Date Calculation

novinosrin — Mon, 16 Dec 2019 17:35:38 GMT

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