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...
So what do you expect for the output? Not clear.
To expand on @ballardw 's succinct but very valid point ... its very hard (actually, its impossible) to generalize solutions when you only present two months to work with. I get the feeling that someone (like me) will present a solution that works on these two months, but not on a case when there are three or more months (and so I have wasted my time working on just your solution from data that covers only two months). And so, you need to present an example which covers the variety of situations that might be encountered with your real data, and so we can generalize from more than two months.
Anyway, here is my solution:
data table_values;
INPUT date currency $ value ;
endmonth=intnx('month',date,0,'e');
CARDS;
21914 USD 200
21914 USD 100
21914 USD 50
21934 USD 50
21934 USD 500
21935 USD 200
;
run;
proc summary nway data=table_values;
class date;
var value;
id endmonth;
output out=sums sum=monthsum;
run;
proc sql;
create table want as select
a.date,a.monthsum,b.monthsum as prevmonthsum
from sums as a left join sums as b
on a.endmonth=intnx('month',b.endmonth,1,'e')
order by a.date;
quit;
@PhiSlogan wrote:
I expect to get the value for the last day of the previous month
But there are 3 values in your example data for the last day. And since you show a result that is probably incorrect it is not clear what you expect.
This does what you want:
data table_values;
input date currency $ value ;
datalines;
21914 USD 200
21914 USD 100
21914 USD 50
21934 USD 50
21934 USD 500
21935 USD 200
;
data want;
set table_values (
);
by date;
set
table_values (
firstobs=2
keep=date
rename=(date=_date)
)
table_values (
obs=1
keep=date
rename=(date=_date)
)
;
retain PrevMonthEndValue _value;
_value + value;
if last.date
then do;
value = _value;
output;
if month(date) ne month(_date)
then do;
PrevMonthEndValue = _value;
end;
_value = 0;
end;
drop _value _date;
format _date date yymmdd10.;
run;
Result:
date currency value PrevMonthEndValue 2019-12-31 USD 350 . 2020-01-20 USD 550 350 2020-01-21 USD 200 350
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.