how to calculate or compare the interval in no. of days between 2 date field column in SAS PROC SQL?

[vini5]

Executed the below SAS script with case statement to populate the value of subscriber_type as 'Primary' if  the date fields CFR_CREATE_DATE = BAN_START_DATE 

and if the difference between CFR_CREATE_DATE minus BAN_START_DATE <= 30 days then the value of subscriber_type will be 'AAL <= 30'

and if the difference between CFR_CREATE_DATE minus BAN_START_DATE > 30

then 'AAL > 30' else Null. How can this be done in PROC SQL?

Note: CFR_CREATE_DATE & BAN_START_DATE used in the below script are both date data type fields

SAS SCRIPT:

proc sql;
connect to oracle(&xyz);
create table CANCELS_SUB as
Select 
CASE
WHEN TRUNC(b.CFR_CREATE_DATE) = TRUNC(b.BAN_START_DATE) THEN 'PRIMARY'
WHEN TRUNC(b.CFR_CREATE_DATE) <> TRUNC(b.BAN_START_DATE)
AND (TRUNC(b.CFR_CREATE_DATE) - TRUNC(b.BAN_START_DATE)) <= 30 THEN 'AAL <= 30'
WHEN TRUNC(b.CFR_CREATE_DATE) <> TRUNC(b.BAN_START_DATE)
AND (TRUNC(b.CFR_CREATE_DATE) - TRUNC(b.BAN_START_DATE)) > 30 THEN 'AAL > 30'
ELSE NULL
END as SUBSCRIBER_TYPE

from WORK.'COPY OF TRANSACTIONAL SURVEY DAT'n a
left join xys.DAILY_CONSUMER_POST_CANCELS b
ON a.BAN = b.BAN ;

Error Log:
NOTE: The "<>" operator is interpreted as "not equals".
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Expression using subtraction (-) requires numeric types.
2 The SAS System 11:31 Wednesday, July 21, 2021

ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: The following columns were not found in the contributing tables: NULL.

[ballardw · Accepted solution]

If you want to compare dates then why are you using "trunc"? That function is designed to truncate a numeric value to a specified number of bytes. Which expects a "number of bytes" as an argument and most of your "dates" would no longer represent the same date if truncated.

If the value returned from Oracle is actually a datetime value then you want to use either the function DATEPART(variable) to get just the date portion of a value that includes time, or use the INTCK function such as

if intck('dtday',b.CFR_CREATE_DATE,b.BAN_START_DATE)=0 then "Primary"

The Intck function returns number of intervals between two date, datetime or time values. The first parameter is the type of interval. If the values are datetime you use DTday, DTmonth, DTweek, DTyear to find the number of intervals based on day, month, week or year. If the values were simple dates then intervals are like day, week, month, year.

[PaigeMiller]

ERROR: Function TRUNC requires at least 2 argument(s).

It requires two arguments, and your calls to TRUNC have only 1 argument. So what are the two arguments? Let's read the documentation at https://documentation.sas.com/doc/en/pgmmvacdc/9.4/lefunctionsref/n1lfp83m4yht68n1luxn5tnmuhp3.htm

Honestly, I don't think TRUNC is what you want here, but its not clear to me what other function is appropriate here. Can you state, in words, what you want TRUNC (or a replacement function) to do?

[vini5]

I'm using TRUNC to perform the function of removing the timestamp from the date field to retain only the date portion. 

[Reeza]

That is the Oracle definition of the TRUNC function but that's not what the TRUNC function does in SAS SQL so you need to clarify my point regarding pass through versus non pass through.


SAS: https://documentation.sas.com/doc/en/pgmmvacdc/9.4/lefunctionsref/n1lfp83m4yht68n1luxn5tnmuhp3.htm
TRUNC Function
Truncates a numeric value to a specified number of bytes.

Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. Please refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.

[Reeza]

Looks like you're trying to use Pass Through SQL incorrectly so it's using the SAS TRUNC function when you want to use the ORACLE version. 

---

SAS SCRIPT:

proc sql;
connect to oracle(&xyz);

---

But you're also using a WORK table and a SQL table, which cannot occur in Pass through. 

---

from WORK.'COPY OF TRANSACTIONAL SURVEY DAT'n a
left join xys.DAILY_CONSUMER_POST_CANCELS b
ON a.BAN = b.BAN ;

---

So you need to not use SQL pass through for sure, ensure you're using SAS functions - so likely DATEPART and not TRUNC. 

First figure out the libname/connection statement and then the query below should be pretty close to what you need. Its common for DB to store dates as datetimes, so I think you do need the datepart function here.

libname odbc myDB dsn=myData pwd= user= schema=dbo;

proc sql;
create table CANCELS_SUB as
Select 
CASE
WHEN datepart(b.CFR_CREATE_DATE) = datepart(b.BAN_START_DATE) THEN 'PRIMARY'
WHEN datepart(b.CFR_CREATE_DATE) ne datepart(b.BAN_START_DATE)
AND (datepart(b.CFR_CREATE_DATE) - datepart(b.BAN_START_DATE)) <= 30 THEN 'AAL <= 30'
WHEN datepart(b.CFR_CREATE_DATE) ne datepart(b.BAN_START_DATE)
AND (datepart(b.CFR_CREATE_DATE) - datepart(b.BAN_START_DATE)) > 30 THEN 'AAL > 30'
ELSE ''
END as SUBSCRIBER_TYPE


from WORK.'COPY OF TRANSACTIONAL SURVEY DAT'n a
left join xys.DAILY_CONSUMER_POST_CANCELS b
ON a.BAN = b.BAN ;

---

@vini5 wrote:

Executed the below SAS script with case statement to populate the value of subscriber_type as 'Primary' if  the date fields CFR_CREATE_DATE = BAN_START_DATE 

and if the difference between CFR_CREATE_DATE minus BAN_START_DATE <= 30 days then the value of subscriber_type will be 'AAL <= 30'

and if the difference between CFR_CREATE_DATE minus BAN_START_DATE > 30

then 'AAL > 30' else Null. How can this be done in PROC SQL?

Note: CFR_CREATE_DATE & BAN_START_DATE used in the below script are both date data type fields

SAS SCRIPT:

proc sql;
connect to oracle(&xyz);
create table CANCELS_SUB as
Select 
CASE
WHEN TRUNC(b.CFR_CREATE_DATE) = TRUNC(b.BAN_START_DATE) THEN 'PRIMARY'
WHEN TRUNC(b.CFR_CREATE_DATE) <> TRUNC(b.BAN_START_DATE)
AND (TRUNC(b.CFR_CREATE_DATE) - TRUNC(b.BAN_START_DATE)) <= 30 THEN 'AAL <= 30'
WHEN TRUNC(b.CFR_CREATE_DATE) <> TRUNC(b.BAN_START_DATE)
AND (TRUNC(b.CFR_CREATE_DATE) - TRUNC(b.BAN_START_DATE)) > 30 THEN 'AAL > 30'
ELSE NULL
END as SUBSCRIBER_TYPE

from WORK.'COPY OF TRANSACTIONAL SURVEY DAT'n a
left join xys.DAILY_CONSUMER_POST_CANCELS b
ON a.BAN = b.BAN ;

Error Log:
NOTE: The "<>" operator is interpreted as "not equals".
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Expression using subtraction (-) requires numeric types.
2 The SAS System 11:31 Wednesday, July 21, 2021

ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Function TRUNC requires at least 2 argument(s).
ERROR: Expression using subtraction (-) requires numeric types.
ERROR: The following columns were not found in the contributing tables: NULL.

---