topic Range selection based of lowest price in SAS Programming

Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 12:08:59 GMT

I have the below table which gives you prices for a product from a date range. But as you can see there are multiple price is available for the same date.So I wanted to have a new range where price is lowest for that day i.e.

if we talk about 02-jan-2018 for product A there are price 28,26,28,10 but i will consider the price 10 because thats the lowest in that range.

Product	on_sale_date	off_sale_date	Price
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

So my table will look like this ( it creates a new range which takes the lowest price in that period.)

Product	new_on_sale_date	new_off_sale_date	Price
A	01-Jan-18	01-Jan-18	28
A	02-Jan-18	10-Jan-18	10
A	11-Jan-18	14-Jan-14	14
B	02-Jan-18	03-Jan-18	27
B	04-Jan-18	12-Jan-18	16
B	13-Jan-18	13-Jan-18	30
c	02-Jan-18	06-Jan-18	24
c	07-Jan-18	08-Jan-18	24

Note:

1) There is no limitation for number of records at any level.

2) Unique level is Product+on_sale_date+off_sale_date+price.

3) I am not considering a solution

Where i need to put all the record on day wise first

And then choose the lowest one

And then create range again.

I wanted to have this data without creating any table on day wise because the size of data is too huge to create on day wise.

Basically I am looking for an optimised query which can hendle 300 Gb data rather than just a simple query .

Re: Range selection based of lowest price

RW9 — Fri, 15 Mar 2019 12:35:18 GMT

An SQL query in general would sort the data behind the scenes just the same as you would yourself, it just hides that step from you.

Why do you have a need to use SQL for this, is the data not in SAS? If so then consider an SQL forum. If its in SAS, then use SAS.

As for the data, your going to have to do some sort of processing on it, no getting away from that. I would expand the data so each date is a single element, e.g:

data inter (drop=on_sale_date off_sale_date);
  set have;
  retain group;
  by product;
  group=ifn(first.product,1,group+1);
  do date=on_sale_date to off_sale_date;
    output;
  end;
run;

Then sort out your data from there, taking start of range/end of range.

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 12:36:52 GMT

Not very specific for SQL, datastep is good for me.Let me check and confirm

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 13:08:05 GMT

Its giving me day wise result and I specifically mentioned i dont want to go in that route. I need range without creating a day wise record.

Re: Range selection based of lowest price

PaigeMiller — Fri, 15 Mar 2019 13:15:43 GMT

Once you have the day-wise result, you can convert it into ranges.

But I doubt there's a way to get the result you want without first getting the day-wise results.

Re: Range selection based of lowest price

RW9 — Fri, 15 Mar 2019 13:46:34 GMT

Well, as I said, your going to have to do some work on the data. Simplest method is daywise I am afraid. You could look at hash lookups, not my style of coding so I tend to avoid that. And you would have to loop over the data to find each match, then keep min/max of start/ends and values, then output again. Not sure if it would save you anything and would be more complex coding.

http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n1b4cbtmb049xtn1vh9x4waiioz4.htm

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 14:11:19 GMT

Daywise data i have created, but the challenge is to create the data the way i am looking.

Purpose to use less hard-disk, i have limited storage for this operation.

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 14:11:39 GMT

Daywise data i have created, but the challenge is to create the data the way i am looking.

Purpose to use less hard-disk, i have limited storage for this operation.

Re: Range selection based of lowest price

RW9 — Fri, 15 Mar 2019 14:23:10 GMT

So you have lots and lots of RAM available then? You need something in order to process it, be it disk space or ram. If you have lots of ram then look at the hash method.

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 14:28:35 GMT

I can try, please share how can i do it.

Re: Range selection based of lowest price

RW9 — Fri, 15 Mar 2019 14:31:22 GMT

Please see the link provided on how to use the hash object:

http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n1b4cbtmb049xtn1vh9x4waiioz4.htm

Re: Range selection based of lowest price

DBailey — Fri, 15 Mar 2019 14:37:17 GMT

Couldn't follow much of the discussion....but this is a simple coding exercise for proc sql. It may take a while to run on 300GB of data...but you would have to process that data anyway to get the results you want.

Proc sql noprint;
create table want as
select
product,
on_sale_date,
off_sale_date,
min(Price) as Price
from have
group by
product,
on_sale_date,
off_sale_date
;
quit;

Re: Range selection based of lowest price

PeterClemmensen — Fri, 15 Mar 2019 15:14:37 GMT

@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?

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 15:54:51 GMT

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.

Re: Range selection based of lowest price

Srigyan — Fri, 15 Mar 2019 15:56:51 GMT

request you to please have a look on the query.

Re: Range selection based of lowest price

DBailey — Fri, 15 Mar 2019 16:00:30 GMT

I don't understand what you're asking me to do.

Re: Range selection based of lowest price

ballardw — Fri, 15 Mar 2019 22:32:56 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Range selection based of lowest price

PeterClemmensen — Fri, 15 Mar 2019 23:19:20 GMT

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.

Re: Range selection based of lowest price

PaigeMiller — Sat, 16 Mar 2019 11:43:05 GMT

@Srigyan wrote:

Daywise data i have created, but the challenge is to create the data the way i am looking.

Purpose to use less hard-disk, i have limited storage for this operation.

Use less hard disk? That should have been mentioned right at the beginning.

But somehow, you have to have enough space on either the disk or in memory to hold all your data and then sort it. If you don't have enough space, then you can't complete the task, it's not a programming problem any more, it is a hardware limitation.

Re: Range selection based of lowest price

Srigyan — Mon, 18 Mar 2019 09:53:30 GMT

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.