My take on an SQL solution:
proc sql;
create table work.want as
select a.id, a.pre_price, a.pre_year, b.post_price, b.post_year
from (
select id, price as Pre_price, yearmonth as Pre_year
from have
group by id
having yearmonth = min(yearmonth)
) as a
left join
(
select id, price as post_price, yearmonth as post_year
from have
group by id
having yearmonth = max(yearmonth)) as b
on a.id=b.id
;
quit;
@kajal_30 wrote:
Thank you can also please share the data step code and as my code is selecting all obs.
data want;
set have ;
by yearmonth ;
if first.yearmonth then pre_price = price and pre_year = yearmonth;
output;
if last.yearmonth then post_price = price and post_year = yearmonth ;
output;
run;
TIA
kajal
Doesn't consider ID at all which your desired seems to imply the ID is important.
If you want the first and last observations you would have to RETAIN the PRE_ variables until you output with the last per ID.
Your example data as presented actually would throw errors because the data is not sorted by yearmonth. If it were, then you would have scrambled the ID values.
Note that eventually bad things come those you think a number like Yearmonth is a good way to handle date information.
