Statistical programming, matrix languages, and more

SAS creating a dynamic interval

Reply
Occasional Contributor
Posts: 5

SAS creating a dynamic interval

This is somewhat complex (well to me at least).

Here is what I have to do: Say that I have the following dataset:

date price volume

02-Sep 40 100

03-Sep 45 200

04-Sep 46 150

05-Sep 43 300

Say that I have a breakpoint where I wish to create an interval in my dataset. For instance, let my breakpoint = 200 volume transaction.

What I want is to create an ID column and record an ID variable =1,2,3,... for every breakpoint = 200. When you sum all the volume per ID, the value must be constant across all ID variables.

So using my example above, my final dataset should look like the following:

date price volume id

02-Sep 40 100 1

03-Sep 45 100 1

03-Sep 45 100 2

04-Sep 46 100 2

04-Sep 46 50 3

05-Sep 43 150 3

05-Sep 43 150 4 (last row can miss some value but that is fine. I will kick out the last id)

As you can see, I had to "decompose" some rows (like the second row for instance, I break the 200 into two 100 volume) in order to have constant value of the sum, 200, of volume across all ID.

Can anyone help me out?

SAS Super FREQ
Posts: 3,221

Re: SAS creating a dynamic interval

Are you using PROC IML? If so, please post some sample code. If you are using the DATA step, you might want to post these questions to the DATA step forum at https://communities.sas.com/community/support-communities/sas_macro_facility_data_step_and_sas_langu...

Occasional Contributor
Posts: 5

Re: SAS creating a dynamic interval

Hi Rick, I could use IML. But you are right. I have to post my question in the appropriate section - sas macro facility. Hopefully someone can help out. I've been struggling with this one!

Post a Question
Discussion Stats
  • 2 replies
  • 212 views
  • 0 likes
  • 2 in conversation