Geting Previous Month Value with Joins

[PhiSlogan]

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...

[ballardw]

So what do you expect for the output? Not clear.

[PaigeMiller]

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]

I expect to get the value for the last day of the previous month

[ballardw]

---

@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.

[PhiSlogan]

Please help me understand where it is not clear for you... This output has 2 values as the *last day of last month values* not 3.. The 1st value is the current value for the 31Dec2019.

date Value PrevMonthEndValue
31DEC2019 350 .
20JAN2020 550 350
21JAN2020 200 350

[Kurt_Bremser]

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