Range selection based of lowest price

[PeterClemmensen]

@Srigyan, first of all, this clearly has some relation to your thread from yesterday range change . I have provided a hash object solution that may suit this problem as well with a few twists. If your found your answer to that question, please mark one of them as a solution 🙂

Secondly, I am not quite sure about the logic in this question. Can you be a little more specific? Why does product A go from having 6 observations to 3? And what determines the ranges? Eg, why not from the 2nd to the 8th in the second observation in your output data?

[Srigyan]

it has the business relationship 🙂 but thats not the same technically.

I still need to try all the answer given in my previous post. if that work surelly i will confirm that, sorry for delay.

As far as this problem is concern. and why the record is coming from 6 to 3 record for product A.

if you expand that data you have multiple price for same day but we need to consider only one price for one day which is the lowest. and thats why there are only 3 range which cover the entire period here. Hope thats clears the query.

[ballardw]

See if this comes close:

data have;
input Product $ on_sale_date :date9. off_sale_date :date9. Price ;
format on_sale_date off_sale_date date9.;
datalines;
A 01-Jan-18 07-Jan-18 28 
A 02-Jan-18 08-Jan-18 26 
A 02-Jan-18 07-Jan-18 28 
A 04-Jan-18 10-Jan-18 21 
A 02-Jan-18 10-Jan-18 10 
A 05-Jan-18 14-Jan-18 14 
b 02-Jan-18 09-Jan-18 27 
b 05-Jan-18 13-Jan-18 30 
b 04-Jan-18 12-Jan-18 16 
b 05-Jan-18 11-Jan-18 19 
c 02-Jan-18 08-Jan-18 27 
c 02-Jan-18 06-Jan-18 24 
c 02-Jan-18 08-Jan-18 30 
;
run;
data inter (drop=on_sale_date off_sale_date);
  set have;
  by product;
  do date=on_sale_date to off_sale_date;
    output;
  end;
  format date date9.;
run;

proc summary data=inter nway;
   class product date;
   var price;
   output out=summary (drop=_:) min=;
run;

proc summary data=summary nway;
   by product notsorted price;
   var date;
   output out=want (drop=_:) min=New_On_sale_date max=New_Off_sale_date;
run;

Please provide example data in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

You may need to ensure that you Product codes are consistent case in the data. If you have "b" and "B" and they are supposed to be the same product then the code can't tell that. Similar if you have minor spelling differences.

This will NOT interpolate an missing dates at the end of an interval. If there is no interval in the original data that includes Dec 25 and the price on Dec 26 was different than on Dec 24 the result will not have any value for Dec 25 in any interval for new_on_sale_date or new_off_sale_date. If you have date intervals with gaps but the price was the same on both such as in

A 02-Jan-18 10-Jan-18 10 
A 05-Jan-18 14-Jan-18 14 
A 18-Jan-18 28-Jan-18 14 

the result for the price 14 interval would be 11Jan2018 to  28Jan2018. If you don't want that behavior we need more explicit rules.

[Srigyan]

Thanks for putting effor on my query and thats good learning for me.

However for my question the first condition was we can't create a date wise data.

data inter (drop=on_sale_date off_sale_date);
  set have;
  by product;
  do date=on_sale_date to off_sale_date;
    output;
  end;
  format date date9.;
run;

This section of code actually creating that, offcourse this is the right answer but not the one I am looking for. 

[PeterClemmensen]

Ok. Makes sense 🙂

If your only reason to not create day-wise results first is to save disk space, consider using the code provided by @ballardw and create a view instead of an actual data set like this

data inter (drop=on_sale_date off_sale_date) / view=inter;
  set have;
  by product;
  do date=on_sale_date to off_sale_date;
    output;
  end;
  format date date9.;
run;

If that for some reason is not enough, I would consider a hash object approach.

[Srigyan]

I am looking to skip that step, is this not possible. I know this way.

[PeterClemmensen]

It's possible, but definitely more complex. 

But if your only reason not to do it this way is disk space, why not simply use a view? The code and the solution would the so much simpler?

Also, remember to close the thread below if you found your answer. It helps other users navigate the comminty

https://communities.sas.com/t5/SAS-Programming/range-change/m-p/543238#M150155