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

Following an account for 18 months in a different performance table

podarum — Tue, 06 Mar 2018 20:45:18 GMT

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.

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

PGStats — Tue, 06 Mar 2018 20:58:44 GMT

You should invert the two dates

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

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

SuryaKiran — Tue, 06 Mar 2018 21:01:52 GMT

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

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

ballardw — Tue, 06 Mar 2018 21:05:53 GMT

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?

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

podarum — Tue, 06 Mar 2018 21:15:10 GMT

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)