topic Re: date frequency min value in SAS Programming

date frequency min value

Srigyan — Wed, 04 Jul 2018 13:05:55 GMT

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.

Re: date frequency min value

RW9 — Tue, 03 Jul 2018 13:47:52 GMT

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.

Re: date frequency min value

Srigyan — Tue, 03 Jul 2018 13:58:01 GMT

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

Re: date frequency min value

novinosrin — Tue, 03 Jul 2018 14:16:05 GMT

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.

Re: date frequency min value

Srigyan — Thu, 05 Jul 2018 08:50:39 GMT

I reframed the question, Please check and respond.

Re: date frequency min value

novinosrin — Thu, 05 Jul 2018 10:47:51 GMT

Ok, let me try and get back to you

Re: date frequency min value

andreas_lds — Thu, 05 Jul 2018 13:44:12 GMT

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.