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...
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.
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?
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.
thanks
@Srigyan Very welcome. That was a good question. Fun stuff! 🙂
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 18code:
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 18Please help...
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
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.