BookmarkSubscribeRSS Feed
Srigyan
Quartz | Level 8

I have mentioned below dataset in SAS.

 

Table-1

 

Sr NoPRODUCTON SALE DATEOFF SALE DATESELL_PRICE
1P101-Jan-1803-Jan-1884
2P102-Jan-1806-Jan-1893
3P104-Jan-1810-Jan-1846
4P108-Jan-1810-Jan-1814
5P101-Jan-1801-Jan-1815
6P201-Jan-1807-Jan-1884
7P203-Jan-1804-Jan-1893
8P207-Jan-1809-Jan-1846
9P204-Jan-1808-Jan-1814
10P203-Jan-1804-Jan-1815

 

I have a product list(in this case P1 and P2) and there are many date range for the same product with the different price. The time range can overlap each other for the same product.

My task is to make a dataset which contains a date range which will be the

min of 'ON SALE DATE' and the max of ''OFF SALE DATE".

so for the product, P1 date range will be 1-Jan-2018 to 10-Jan-2018.

and for the product, P2 date range will be 01-Jan-2018 to 09-Jan-2018.

and the price will come which will be minimum for that date. so the dataset will look like below one...

 

Table-2

 

Sr NoPRODUCTDateSELL_PRICE
1P101-Jan-1815
2P102-Jan-1884
3P103-Jan-1884
4P104-Jan-1846
5P105-Jan-1846
6P106-Jan-1846
7P107-Jan-1846
8P108-Jan-1814
9P109-Jan-1814
10P110-Jan-1814
11P201-Jan-1884
12P202-Jan-1884
13P203-Jan-1815
14P204-Jan-1814
15P205-Jan-1814
16P206-Jan-1814
17P207-Jan-1814
18P208-Jan-1814
19P209-Jan-1846

 

so if you check

the price for P1 on 1-Jan-2018, there are two prices mentioned for this

first table-Row-1, the price for P1 is 84

First table-Row-Row-5 the price for P1 is 15.

 

hence the final price for P1 for 01-Jan-2018 will be 15 which is minimum between (84,15).

check Table-2 and Row-1.

 

How to write a SAS code for this.

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

I am sorry, i have read your post 3 times and it still doesn't make a single bit of sense to me.  None of what appears in the second set of data appears to have any connection to any of the data points in the first set of data other than product?

 

Perhaps, and am just guessing somewhat here, you want to expand your first set of data out by date so something like:

data want (drop=on_sale_date off_sale_date);
  set have;
  do date=on_sale_date to off_sale_date;
    output;
  end;
run;

Then you can sort that data by date.  Then something like:

data want (drop=price);
  set want;
  if lag(date)=date then do;
    actual_value=mean(price,lag(price));
    output;
  end;
  else do;
    actual_value=price;
    output;
  end;
run;

Please post test data in the for of a datastep in future.

Srigyan
Quartz | Level 8

my apology, I put the wrong table, modified it again. Please have a look.

novinosrin
Tourmaline | Level 20

I think your data doesn't represent your description. If you are expecting anyone in the community to loop through from min(date) to max(date) increment by one and look up the date range and then fetch the min, i'm afraid a better representative sample of your INPUT and a sample of your expected OUTPUT is very much needed. 

 

Basically that will help in testing the developed code against your input sample and test that against the output that you want. Otherwise, I am afraid, i'm not smart enough to make guesses although somebody in the community probably will.

Srigyan
Quartz | Level 8

I reframed the question, Please check and respond.

 

 

novinosrin
Tourmaline | Level 20

Ok, let me try and get back to you

andreas_lds
Jade | Level 19

For future questions: please post data as data-steps 😉

 

data work.have;
   length srno 8 product $ 2 on_sale_date off_sale_date sell_price 8;
   informat on_sale_date off_sale_date anydtdte.;
   format on_sale_date off_sale_date ddmmyyp10.;
   input srno product on_sale_date off_sale_date sell_price;
   datalines;
1 P1 01-Jan-18 03-Jan-18 84
2 P1 02-Jan-18 06-Jan-18 93
3 P1 04-Jan-18 10-Jan-18 46
4 P1 08-Jan-18 10-Jan-18 14
5 P1 01-Jan-18 01-Jan-18 15
6 P2 01-Jan-18 07-Jan-18 84
7 P2 03-Jan-18 04-Jan-18 93
8 P2 07-Jan-18 09-Jan-18 46
9 P2 04-Jan-18 08-Jan-18 14
10 P2 03-Jan-18 04-Jan-18 15
;
run;

data work.step;
   set work.have;
   by product;

   length date 8;
   format date ddmmyyp10.;

   do date = on_sale_date to off_sale_date;
      output;
   end;

   drop srno on_sale_date off_sale_date;
run;

proc sort data=work.step out=work.sorted;
   by product date sell_price;
run;

data work.want;
   length srno 8;
   retain srno 0;

   set work.sorted;
   by product date;

   if first.date;

   srno = srno + 1;
run;

i was to lazy to search for the appropriate format for the dates.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 831 views
  • 0 likes
  • 4 in conversation