Help using Base SAS procedures

Calculating values based on dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

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!

View solution in original post


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
  • 232 views
  • 2 likes
  • 3 in conversation