I have mentioned below dataset in SAS.
Table-1
Sr No | PRODUCT | ON SALE DATE | OFF SALE DATE | SELL_PRICE |
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 |
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 No | PRODUCT | Date | SELL_PRICE |
1 | P1 | 01-Jan-18 | 15 |
2 | P1 | 02-Jan-18 | 84 |
3 | P1 | 03-Jan-18 | 84 |
4 | P1 | 04-Jan-18 | 46 |
5 | P1 | 05-Jan-18 | 46 |
6 | P1 | 06-Jan-18 | 46 |
7 | P1 | 07-Jan-18 | 46 |
8 | P1 | 08-Jan-18 | 14 |
9 | P1 | 09-Jan-18 | 14 |
10 | P1 | 10-Jan-18 | 14 |
11 | P2 | 01-Jan-18 | 84 |
12 | P2 | 02-Jan-18 | 84 |
13 | P2 | 03-Jan-18 | 15 |
14 | P2 | 04-Jan-18 | 14 |
15 | P2 | 05-Jan-18 | 14 |
16 | P2 | 06-Jan-18 | 14 |
17 | P2 | 07-Jan-18 | 14 |
18 | P2 | 08-Jan-18 | 14 |
19 | P2 | 09-Jan-18 | 46 |
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.
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.
my apology, I put the wrong table, modified it again. Please have a look.
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.
I reframed the question, Please check and respond.
Ok, let me try and get back to you
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.