topic Re: Calculating values based on dates in SAS Procedures

Calculating values based on dates

andp — Fri, 20 Jul 2012 19:24:53 GMT

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!

Re: Calculating values based on dates

tish — Fri, 20 Jul 2012 19:39:03 GMT

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;

Re: Calculating values based on dates

andp — Fri, 20 Jul 2012 21:10:27 GMT

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?

Re: Calculating values based on dates

tish — Fri, 20 Jul 2012 21:19:52 GMT

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!

Re: Calculating values based on dates

andp — Fri, 20 Jul 2012 21:39:38 GMT

It works now, thanks!!

Re: Calculating values based on dates

Howles — Mon, 23 Jul 2012 20:31:07 GMT

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 ;

Re: Calculating values based on dates

tish — Mon, 23 Jul 2012 21:11:15 GMT

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.