topic Re: SAS creating a dynamic interval in SAS/IML Software and Matrix Computations

SAS creating a dynamic interval

charlesmartineau — Sun, 17 Jun 2012 16:50:51 GMT

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?

Re: SAS creating a dynamic interval

Rick_SAS — Sun, 17 Jun 2012 17:12:12 GMT

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_language_elements

Re: SAS creating a dynamic interval

charlesmartineau — Sun, 17 Jun 2012 18:07:06 GMT

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!