topic Re: intnx function in SAS Programming

intnx function

Eugenio211 — Fri, 06 Dec 2019 20:51:20 GMT

Hi,

Hope all is well!

I just wanted to ask assistance with this data step:

proc sql;
create table MP_Loans as
select lp.loan_key
,datepart(lp.date_entered) as CHARGEOFF_DATE format mmddyy.
,l.location
,l.product_type
from wcpca.LoanPayment as lp
join wcpca.Loan as l
on lp.loan_key = l.loan_key
and l.trans_detail_key ^= 1
where lp.reversed = 0
and l.product_type = 'M'
and lp.trans_code = 68
and datepart(lp.date_entered) <= intnx('Day',datepart(lp.date_entered),-1)
;quit;

is the highlighted in blue correct? thanks a lot

I'm trying to get records that are 1 day less than the Chargeoff Date

Re: intnx function

ballardw — Fri, 06 Dec 2019 21:46:12 GMT

@Eugenio211 wrote:

Hi,

Hope all is well!

I just wanted to ask assistance with this data step:

proc sql;
create table MP_Loans as
select lp.loan_key
,datepart(lp.date_entered) as CHARGEOFF_DATE format mmddyy.
,l.location
,l.product_type
from wcpca.LoanPayment as lp
join wcpca.Loan as l
on lp.loan_key = l.loan_key
and l.trans_detail_key ^= 1
where lp.reversed = 0
and l.product_type = 'M'
and lp.trans_code = 68
and datepart(lp.date_entered) <= intnx('Day',datepart(lp.date_entered),-1)
;quit;

is the highlighted in blue correct? thanks a lot

I'm trying to get records that are 1 day less than the Chargeoff Date

Syntactically correct in that I won't throw an error but what you are doing in effect is:

and value <= (value -1);

So this condition would never be true: 4 <= 3 for example.

I suspect that you actually intended one of the date_entered in the highlighted code to be L.date_entered (from the Loan data set)

Re: intnx function

PaigeMiller — Fri, 06 Dec 2019 20:59:04 GMT

@Eugenio211 wrote:

Hi,

Hope all is well!

I just wanted to ask assistance with this data step:

proc sql;
create table MP_Loans as
select lp.loan_key
,datepart(lp.date_entered) as CHARGEOFF_DATE format mmddyy.
,l.location
,l.product_type
from wcpca.LoanPayment as lp
join wcpca.Loan as l
on lp.loan_key = l.loan_key
and l.trans_detail_key ^= 1
where lp.reversed = 0
and l.product_type = 'M'
and lp.trans_code = 68
and datepart(lp.date_entered) <= intnx('Day',datepart(lp.date_entered),-1)
;quit;

is the highlighted in blue correct? thanks a lot

I'm trying to get records that are 1 day less than the Chargeoff Date

What happens when you run this code? Does it give you the desired result?

Re: intnx function

Tom — Fri, 06 Dec 2019 21:01:17 GMT

If you have date values there is no need for INTNX to count days. The value stored is already days.

But you could use the DTDAY interval directly with the numbers of seconds in your datetime values instead of first converting them into number of days using the DATEPART() function. But do your DATETIME values that you are feeding into the DATEPART() function actually have time parts? And if so does it matter if the difference is less than 24 hours? Or more than 24 hours? What about value pairs like:

Small Large Hours_different
03JAN2019:01:00 04JAN2019:21:00 44
03JAN2019:21:00 04JAN2019:01:00 4

Re: intnx function

Eugenio211 — Mon, 09 Dec 2019 13:38:08 GMT

Thanks for the clarification.

What I'm trying to do is to get the previous day data.

For example:

Record 1234567 was charged off Jan 02, I want to get the Jan 01 data of that record.

It means that I want to get all the previous day data prior to charge off of all the records in this query.

Re: intnx function

Eugenio211 — Mon, 09 Dec 2019 13:39:07 GMT

I tried to this and it's giving me 0 observations

and lp.date_entered < intnx('dtday',lp.date_entered,-1)

Re: intnx function

Eugenio211 — Mon, 09 Dec 2019 13:39:48 GMT

Hi,

the query runs but it gives me 0 observations.