BookmarkSubscribeRSS Feed
Srigyan
Quartz | Level 8

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.

 

Producton_sale_dateoff_sale_datePrice
A01-Jan-1807-Jan-1828
A02-Jan-1808-Jan-1826
A02-Jan-1807-Jan-1828
A04-Jan-1810-Jan-1821
A02-Jan-1810-Jan-1810
A05-Jan-1814-Jan-1814
b02-Jan-1809-Jan-1827
b05-Jan-1813-Jan-1830
b04-Jan-1812-Jan-1816
b05-Jan-1811-Jan-1819
c02-Jan-1808-Jan-1827
c02-Jan-1806-Jan-1824
c02-Jan-1808-Jan-1830

 

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

 

Productnew_on_sale_datenew_off_sale_datePrice
A01-Jan-1801-Jan-1828
A02-Jan-1810-Jan-1810
A11-Jan-1814-Jan-1414
B02-Jan-1803-Jan-1827
B04-Jan-1812-Jan-1816
B13-Jan-1813-Jan-1830
c02-Jan-1806-Jan-1824
c07-Jan-1808-Jan-1824

 

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 .

 

21 REPLIES 21
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

Srigyan
Quartz | Level 8

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

Srigyan
Quartz | Level 8

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.

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Srigyan
Quartz | Level 8

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. 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

Srigyan
Quartz | Level 8

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. 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Srigyan
Quartz | Level 8

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

DBailey
Lapis Lazuli | Level 10
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;
Srigyan
Quartz | Level 8

request you to please have a look on the query.

DBailey
Lapis Lazuli | Level 10

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

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 21 replies
  • 1950 views
  • 6 likes
  • 6 in conversation