Hello community, I am currently facing an issue getting previous month end value. Please Help with this E.T.L logic I have been on this for the past 2 days... My objective is to get to this Output:
i.e in this example for every date (20 and 21 Jan 2020) the PrevMonthEndValue should be equal to the value for the date (31 Dec 2019)
date Value PrevMonthEndValue
31DEC2019 350 .
20JAN2020 550 350
21JAN2020 200 350
I have this dataset below;
data table_values;
INPUT date currency $ value ;
CARDS;
21914 USD 200
21914 USD 100
21914 USD 50
21934 USD 50
21934 USD 500
21935 USD 200
;
run;
And I need to full join the dataset to get every position so as to get a valid total:
proc sql;
create table work.result_aggregation as
select
COALESCE(t_left.date, (intnx('month', t_left.date,1,'E') ), t_right.date) as t_date format=date9.,
COALESCE(t_left.Currency,t_right.Currency ) as Currency,
t_left.date as left_date format=date9.,
t_right.date as right_date format=date9.,
t_left.value as Value,
t_right.value as PrevMonthEndValue
from
work.table_values as t_left full join
work.table_values as t_right
on
(
intnx('month',t_left.date,-1,'E') = t_right.date
and t_left.Currency = t_right.Currency
)
;
quit;
I then have a sum for the total
proc sql;
create table work.summarized as
select
t_date,
(SUM(Value)) as Value,
(SUM(PrevMonthEndValue)) as PrevMonthEndValue
from work.result_aggregation
group by
t_date
order by
t_date
;
quit;
And this is my output...
... View more
![SASGF 2020 [Virtual] Attendee](/html/@4229D83652827A3CCCDB8CAD5D23E8D2/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
