DATA Step, Macro, Functions and more

Following an account for 18 months in a different performance table

Accepted Solution Solved
Reply
Super Contributor
Posts: 409
Accepted Solution

Following an account for 18 months in a different performance table

[ Edited ]

Hi,  I have 300 applications that applied within a 12 month window.  Eg. Aug'2015 t Jul'2016 (call these APP_Dates). These are in TABLE A.  I also have TABLE B with a whole bunch of performance data for 3 years starting Sep'2015 all the way up to Jan'2018 (call these PERF_Dates).  I want to find the performance for each application for exactly 18 months from the APP_Date + 1 .  So an application from Aug'2015 I would have their performance info from Sep'2015 to Feb'2017.. and so on for each account.   It is possible that some applications do not have any performance data or some may only have a smaller limited amount (eg. less then 18 months).

 

  I tried the below but that still gave me everyone.

where intck('month',a.CreationDate,b.Proc_Dt) <= 19

 

Thanks.


Accepted Solutions
Solution
‎03-16-2018 11:32 PM
Esteemed Advisor
Posts: 5,611

Re: Following an account for 18 months in a different performance table

You should invert the two dates

 

where intck('month', b.Proc_Dt, a.CreationDate) <= 19

PG

View solution in original post


All Replies
Solution
‎03-16-2018 11:32 PM
Esteemed Advisor
Posts: 5,611

Re: Following an account for 18 months in a different performance table

You should invert the two dates

 

where intck('month', b.Proc_Dt, a.CreationDate) <= 19

PG
PROC Star
Posts: 617

Re: Following an account for 18 months in a different performance table

Please provide some example data you have and the output you want. We never no how you data looks like unless you show us.

Thanks,
Suryakiran
Super User
Posts: 13,868

Re: Following an account for 18 months in a different performance table

So, which of a.creationdate and b.Proc_dt is your APP_date? Your PERF_date? Discussing date in one term and the showing code using different variable names doesn't help.

 

If your all of you b.Proc_dt is before a.creationdate then all of the intck values are negative and you would get the behavior you describe (assumes the data is joined on some other variables that make sense).

 

You might have to post some example data in the form of data steps and the actual code you ran for a good diagnostic.

 

Were there any interesting notes in the log?

Super Contributor
Posts: 409

Re: Following an account for 18 months in a different performance table

Thanks everyone for your help.    I used this and it works fine:

where intnx ('month',app_date,1) <=Perf_date <=intnx('month',app_date,19)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 101 views
  • 0 likes
  • 4 in conversation