intnx function

[Eugenio211]

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

[ballardw]

---

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

[Eugenio211]

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.

[Eugenio211]

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

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

[PaigeMiller]

---

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

[Eugenio211]

Hi,

the query runs but it gives me 0 observations.

[Tom]

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