BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
andp
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
tish
Calcite | Level 5

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

6 REPLIES 6
tish
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

It works now, thanks!!

Howles
Quartz | Level 8

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
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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