Quick proc sql for difference in months

[Distraction]

Hey y'all

I need some proc sql code that will take two dates and tell me the months between. Piece of cake for me in Teradata SQL, but a challenge in SAS.

I'm trying to calculate Statement Number. I have the date the account was opened (Open_Date) and the date of the Statement (Statement_Date).

Thanks!

Matt

 

[ChrisW75]

You can probably use the intck function (count = intck('MONTH',fromdate,todate), though this will only return the number of times there is a 1st of the month between your two dates, so it may also be totally useless (I generally give up on intck, I've no idea what situations it might be useful for).

Also, is it safe to use the time interval for the statement number? What if the customer requests a statement at a different time, wouldn't that throw off your count?

If you still want it, post the teradata code here and I (or someone else) may be able to translate it to SAS friendly code. I've been taught Teradata myself, but haven't used it in years.
Enjoy.

 

[Distraction]

select
(cast((statement_date - opn_dt month(4)) as float) + (case when extract (day from statement_date) = extract (day from opn_dt) then 0 else 1 end)) as Statement_Number

The statement_date - opn_dt month(4) returns the number of months between. However it needs '1' added to it unless the days are the same (ie: open on 10th, statement on the 10th of the next month will return a 0). So the case statement compares the days and returns either a 0 or 1 to be added appropriately.

Good luck!

 

[jymm]

is there a reason you need to do it in proc sql and not in native SAS??? Native sas does a pretty cool job with dates if they are in sas date format...

 

[Distraction]

either way

 

[ChrisW75]

Here you go, this should work

data _null_;
date1 = '20MAR2002'd;
date2 = '21APR2002'd;

diffmon = intck('MONTH',date1,date2) - (day(date1) > day(date2));
put @1 diffmon=;
run;

the part after the minus sign returns a 1 if the day part of date2 is less than the day part of date1. I saw this used somewhere and was pretty impressed witht he simplicity, I used to do some complicated long checks, but this works pretty well.

You may be able to incorporate this into a Proc SQL step. Change the values of Date1 and Date2 to some different values to make sure you are happy with the results accuracy.

Hope this helps.