Calculating values based on dates

[andp]

Hi all,

I'm currently trying to split a monthly value into a daily equivalent and was wondering if there's an easy way to do this in SAS.

What I have are rows per day for each ID and a monthly value that needs to be divided by the number of days in a given month.

Dataset

ID          Date          Value

1          1/1/2001     5

1          1/2/2001     5

etc is what I currently have, but I'd like it to be

Dataset

ID          Date          Value

1          1/1/2001     5/31

1          1/2/2001     5/31

for January and

1          2/1/2001     3/28

1          2/2/2001     3/28

etc.

Thanks!

[tish]

proc sql;

   create table new_dataset as

      select

         ID,

         date,

         value,

         intnx('month', date, 0, 'end') - intnx('month', date, 0, 'beginning') + 1 as days,

         value / days as new_value

      from

         old_dataset;

quit;

[andp]

Hi tish,

I'm getting an error "ERROR: The following columns were not found in the contributing tables: days."

Is it something to do with the intnx line before it not creating the variable properly?

[tish · Accepted solution]

Oh no. My mistake! In the select clause, once the variable days is created, we need to declare it as a calculated variable if we refer to it. Look at the correction in the calculation for new_value:

proc sql;

   create table new_dataset as

      select

         ID,

         date,

         value,

         intnx('month', date, 0, 'end') - intnx('month', date, 0, 'beginning') + 1 as days,

         value / calculated days as new_value

      from

         old_dataset;

quit;

Blush! My apologies!

[andp]

It works now, thanks!!

[Howles]

The DAY function can simplify the formula. I would use it directly in the divisor, to avoid creating an intermediate column.

create table new_dataset as

select  ID,

         date,

         value,

         value / day( intnx('month', date, 0, 'end') ) as new_value

  from old_dataset ;

quit ;

[tish]

Oh, the DAY() function! I like that! About the intermediate column: when I'm the one doing the work, I like to open up and look at my intermediate files, so I can make sure that my code is doing what I think it is.