SAS creating a dynamic interval

[charlesmartineau]

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?

[Rick_SAS]

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...

[charlesmartineau]

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!