change time series data into range

[Srigyan]

I have a following time series data.

Product	DATE	price
A	01-Jan-18	10
A	02-Jan-18	10
A	03-Jan-18	10
A	04-Jan-18	10
A	05-Jan-18	10
A	06-Jan-18	10
A	07-Jan-18	3
A	08-Jan-18	11
A	10-Jan-18	11
B	15-Jan-18	15
B	16-Jan-17	15
B	17-Jan-17	15
B	20-Jan-17	15
B	21-Jan-17	19
B	22-Jan-17	19
B	23-Jan-17	19
B	24-Jan-17	18

code:

data Table1;

input product $ date date9.  price;
format date date9.;
datalines;
A 01-Jan-18 10
A 02-Jan-18 10
A 03-Jan-18 10
A 04-Jan-18 10
A 05-Jan-18 10
A 06-Jan-18 10
A 07-Jan-18 3
A 08-Jan-18 11
A 10-Jan-18 11
B 15-Jan-18 15
B 16-Jan-17 15
B 17-Jan-17 15
B 20-Jan-17 15
B 21-Jan-17 19
B 22-Jan-17 19
B 23-Jan-17 19
B 24-Jan-17 18
;

run;

I wanted to have this data converted in to range.

Product	Start_date	End_date	Price
A	01-Jan-18	06-Jan-18	10
A	07-Jan-18	07-Jan-18	3
A	08-Jan-18	08-Jan-18	11
A	10-Jan-18	10-Jan-18	11
B	15-Jan-18	17-Jan-17	15
B	20-Jan-17	20-Jan-17	15
B	21-Jan-17	23-Jan-17	19
B	24-Jan-17	24-Jan-17	18

Please help...

[novinosrin]

Hi @Srigyan   Can you explain the logic of this result

A	08-Jan-18	08-Jan-18	11
A	10-Jan-18	10-Jan-18	11

I am unable to comprehend as I would think the price of 11 lasted from 8th to 10th? 

OH well, I see , so you are basically taking only continuous data?

[Srigyan]

Yes

[novinosrin · Accepted solution]

Hello @Srigyan 

data Table1;

input product $ date date9.  price;
format date date9.;
datalines;
A 01-Jan-18 10
A 02-Jan-18 10
A 03-Jan-18 10
A 04-Jan-18 10
A 05-Jan-18 10
A 06-Jan-18 10
A 07-Jan-18 3
A 08-Jan-18 11
A 10-Jan-18 11
B 15-Jan-17 15
B 16-Jan-17 15
B 17-Jan-17 15
B 20-Jan-17 15
B 21-Jan-17 19
B 22-Jan-17 19
B 23-Jan-17 19
B 24-Jan-17 18
;

run;




data temp;
set table1;
by product price notsorted;
retain grp;
k=dif(date);
if first.product then grp=1;
else if k ne 1 or first.price then grp+1;
drop k;
run;

proc sql;
create table want as
select distinct product,min(date) as  Start_date	format=date9.,max(date) as End_date format=date9., price
from temp
group by product, grp
order by product, start_date;
quit;

Please note,

Your table B 15,16, should have the year 17 and not 18 to meet your output.

[Srigyan]

thanks

[novinosrin]

@Srigyan   Very welcome. That was a good question. Fun stuff! 🙂

[Reeza]

proc sort data=table1; by product date;
run;

data want;
set table1;
by product date price notsorted;
retain start_date;

if first.price then start_date=date;

if last.price then do;
    end_date = date; 
    output;
end;

drop date;
run;

Use the NOTSORTED option to identify changes in prices. 

Use the RETAIN to hold the start_date the same until it ends.

Use OUTPUT to explicitly output at the end of a series.

---

@Srigyan wrote:

I have a following time series data.

Product	DATE	price
A	01-Jan-18	10
A	02-Jan-18	10
A	03-Jan-18	10
A	04-Jan-18	10
A	05-Jan-18	10
A	06-Jan-18	10
A	07-Jan-18	3
A	08-Jan-18	11
A	10-Jan-18	11
B	15-Jan-18	15
B	16-Jan-17	15
B	17-Jan-17	15
B	20-Jan-17	15
B	21-Jan-17	19
B	22-Jan-17	19
B	23-Jan-17	19
B	24-Jan-17	18

code:

data Table1;

input product $ date date9.  price;
format date date9.;
datalines;
A 01-Jan-18 10
A 02-Jan-18 10
A 03-Jan-18 10
A 04-Jan-18 10
A 05-Jan-18 10
A 06-Jan-18 10
A 07-Jan-18 3
A 08-Jan-18 11
A 10-Jan-18 11
B 15-Jan-18 15
B 16-Jan-17 15
B 17-Jan-17 15
B 20-Jan-17 15
B 21-Jan-17 19
B 22-Jan-17 19
B 23-Jan-17 19
B 24-Jan-17 18
;

run;

I wanted to have this data converted in to range.

Product	Start_date	End_date	Price
A	01-Jan-18	06-Jan-18	10
A	07-Jan-18	07-Jan-18	3
A	08-Jan-18	08-Jan-18	11
A	10-Jan-18	10-Jan-18	11
B	15-Jan-18	17-Jan-17	15
B	20-Jan-17	20-Jan-17	15
B	21-Jan-17	23-Jan-17	19
B	24-Jan-17	24-Jan-17	18

Please help...

---

[Srigyan]

didn't work for me.output is coming like this. date is fine but this is not giving required output.

product	price	start_date	end_date
A	10	21185	21185
A	10	21186	21186
A	10	21187	21187
A	10	21188	21188
A	10	21189	21189
A	10	21190	21190
A	3	21191	21191
A	11	21192	21192
A	11	21194	21194
B	15	20835	20835
B	15	20836	20836
B	15	20839	20839
B	19	20840	20840
B	19	20841	20841
B	19	20842	20842
B	18	20843	20843
B	15	21199	21199