I figured it out.  it was my formatting.  Your code works to a T!!  Thank you so much! ... View more

I tried using pgstats code and I keep getting an error.  I think the problem is that I am somehow formatting the dates wrong.  Here is an example of what the date looks like in the data set: 20071225 ie year 2007 month 12 day 25.  Originally it was formatted as a numeric but I used your code to for the format and informat: informat startdate enddate yymmdd8.; format startdate enddate yymmdd10.; and then tried the data step you suggested.  However, when I do I get the following error continually in the log: NOTE: Invalid argument to function INTNX at line 140 column 13. NOTE: Invalid argument to function INTNX at line 141 column 34. New_ID=123353443 billyr=2007 billmo=200710 bill_per_beg=********** bill_per_end=********** bill_days=29 daysToEnd=. daysToBeginning=. _ERROR_=1 _N_=1 and the resulting dataset has missing values "." for both daystoend and daystobeginning.  I'm sorry to ask again but I am at a loss.  I also tried just using the intnx function to get the beginning of the month but it still didn't work. I also also tried to jsut get the month from the bill_per_beg(Bill period beginning aka startdate) and both times I kept getting an error which is why I think it has something to do with how I'm formatting the columns.  But when I look at column attributes it says the format and informat are yymmdd10. and yymmdd8. ... View more

I tried using your code and I keep getting an error.  I think the problem is that I am somehow formatting the dates wrong.  Here is an example of what the date looks like in the data set: 20071225 ie year 2007 month 12 day 25.  Originally it was formatted as a numeric but I used your code to for the format and informat: informat startdate enddate yymmdd8.; format startdate enddate yymmdd10.; and then tried the data step you suggested.  However, when I do I get the following error continually in the log: NOTE: Invalid argument to function INTNX at line 140 column 13. NOTE: Invalid argument to function INTNX at line 141 column 34. New_ID=123353443 billyr=2007 billmo=200710 bill_per_beg=********** bill_per_end=********** bill_days=29 daysToEnd=. daysToBeginning=. _ERROR_=1 _N_=1 and the resulting dataset has missing values "." for both daystoend and daystobeginning.  I'm sorry to ask again after you were kind enough to put the code up but I am at a loss.  I also tried just using the intnx function to get the beginning of the month but it still didn't work. I also also tried to jsut get the month from the bill_per_beg(Bill period beginning aka startdate) and both times I kept getting an error which is why I think it has something to do with how I'm formatting the columns.  But when I look at column attributes it says the format and informat are yymmdd10. and yymmdd8. ... View more

You are correct on both counts, if by billing cycle you mean number of of days in the cycle.  The billing cycles are determined by when the customer first signed up, but the data itself seems to give or take a few days sometimes. ... View more

USER   STARTDATE  ENDDATE   AVGTEMP 1          1/12/10          2/12/10          ? 2          1/08/10          2/10/10          ? 3          1/15/10          2/18/10          ? I am trying to get the average temperature(for example) over the billing period.  Thus I need the number of days from each month in the billing cycle so I can get the average temp for each user in each cycle, then divide by the number of days in the billing cycle and multiply by 30 to normalize.  Is that what you meant by example data?  As for a complete description of the billing cycle, I'm working with a cable tv company data and the billing months for each s\user is pretty much unique as it just starts on the day of each month whenever they started service. ... View more