DATA Step, Macro, Functions and more

date frequency min value

Reply
Contributor
Posts: 20

date frequency min value

[ Edited ]

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.

Super User
Super User
Posts: 9,599

Re: date frequency min value

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.

Contributor
Posts: 20

Re: date frequency min value

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

PROC Star
Posts: 1,781

Re: date frequency min value

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.

Contributor
Posts: 20

Re: date frequency min value

[ Edited ]
Posted in reply to novinosrin

I reframed the question, Please check and respond.

 

 

PROC Star
Posts: 1,781

Re: date frequency min value

Ok, let me try and get back to you

Valued Guide
Posts: 564

Re: date frequency min value

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.

Ask a Question
Discussion stats
  • 6 replies
  • 108 views
  • 0 likes
  • 4 in conversation