## Calculating values based on dates

Solved
Occasional Contributor
Posts: 18

# Calculating values based on dates

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!

Accepted Solutions
Solution
‎07-20-2012 05:19 PM
Contributor
Posts: 52

## Re: Calculating values based on dates

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!

All Replies
Contributor
Posts: 52

## Re: Calculating values based on dates

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;

Occasional Contributor
Posts: 18

## Re: Calculating values based on dates

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?

Solution
‎07-20-2012 05:19 PM
Contributor
Posts: 52

## Re: Calculating values based on dates

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!

Occasional Contributor
Posts: 18

## Re: Calculating values based on dates

It works now, thanks!!

Regular Contributor
Posts: 184

## Re: Calculating values based on dates

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 ;

Contributor
Posts: 52

## Re: Calculating values based on dates

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 6 replies
• 307 views
• 2 likes
• 3 in conversation