topic Number of months between maximum and minimum of dates in a group in SAS Programming

Number of months between maximum and minimum of dates in a group

farshidowrang — Fri, 21 Feb 2020 23:42:28 GMT

Dear friends

I have different wells which produce oil. I like to find number of month between two maximum and minimum dates in each WELL group.

My dataset (_INPUT1) is:

WELL_NAME	PROD_DATE
A	03JUL1981:00:00:00
A	09JAN1982:00:00:00
A	05SEP1992:00:00:00
A	12JAN1984:00:00:00
B	16JUL1993:00:00:00
B	18FEB1988:00:00:00
B	21NOV1986:00:00:00
B	29MAY1983:00:00:00
C	11OCT1980:00:00:00
C	12JUN1993:00:00:00
C	01DEC1988:00:00:00
C	02MAY1993:00:00:00
D	03AUG1999:00:00:00
D	03JAN1980:00:00:00
D	06NOV2005:00:00:00

My code is following. But it does not work:

proc sql;
create table _OUTPUT1 as
select WELL_NAME, PROD_DATE
intck('month',MAX(PROD_DATE),MIN(PROD_DATE)) as MTHS_PROD
FROM _INPUT1
GROUP BY WELLBORE_NAME
ORDER BY WELLBORE_NAME
;

quit;

Can you please help me with this?

Tank you very much in advance!

best regards

Farshid Owrang

Re: Number of months between maximum and minimum of dates in a group

SASKiwi — Sat, 22 Feb 2020 03:46:25 GMT

Try DTMONTH instead of MONTH as the interval in your INTCK function. Your dates are actually datetimes so you need a datetime interval: https://documentation.sas.com/?docsetId=leforinforref&docsetTarget=p0g056g35ez8son1sfavozh0lfb3.htm&docsetVersion=9.4&locale=en#n1d3z2e1f8u8drn1j70702vf4q3h

Re: Number of months between maximum and minimum of dates in a group

PGStats — Sat, 22 Feb 2020 04:55:34 GMT

If you want to count full months, and not the number of month boundaries crossed, you will also need option CONTINUOUS

intck('dtmonth', MIN(PROD_DATE), MAX(PROD_DATE), "C")

note : putting the min before the max will give you a positive count.