Writing a SAS code to show a point-in-time

[jeje]

I have a dataset and want it to show where the prior end of period becomes the current beginning of period. An example is indicated below:

Date                     BOP                          EOP

1/31/2022             1000                         1500

2/28/2022             1500                         2000

3/31/2022             2000                         2300

4/30/222               2300                         2100

I want to avoid creating two different tables of data. I want the data to show say EOP 1/31/2022 $1500 and the same $1500 begins for end of period 2/28/2022 and in that order as shown above. Is there a way to go about it? 

Here is the SAS code I am currently working with

Libname ***
Libname MYLIB Base ***

%let end_date_beg = %str(%')%sysfunc(intnx(year,%sysfunc(date( )),-1,E),mmddyy10.)%str(%');
%let end_date_ending = %str(%')%sysfunc(intnx(month,%sysfunc(date( )),-1,E),mmddyy10.)%str(%');

%PUT NOTE: end_date_begin is &end_date_beg;
%PUT NOTE: end_date_ending is &end_date_end;

proc sql;
create table MYLIB.Data as
select * from ***
(
SELECT DISTINCT
table1.client_id,
table2.contract_id,
table3.plan_type,
WHERE
table4.status_cd='I'
AND table5.change_dt <= &end_date_beg.
AND table5.next_change_dt > &end_date_beg.
;
);
quit;

How do I modify the date to reflect the point-in-time as explained above?

[PaigeMiller]

Am I understanding you properly that the table you show at the beginning of your message is the desired output? If so, can you also show us the input data set you are working with? Or am I totally way off?

Also, are your dates actually character strings? (that's what they appear to be based upon your code). What does PROC CONTENTS say, is the variable named DATE numeric or character?

[ballardw]

How about providing an example of the data you start with?

As a data step so we don't have to guess about variable types, lengths and other properties.

Since  you have a requirement of " prior end of period becomes the current beginning of period. " it almost certainly means that SQL is not the tool to use as there is no guarantee what order things are processed. SQL stands for "Structured Query Language" not "Sequential Query Language" and operates on sets of records.

Your "example" also does not include anything resembling variables Client_id, Contract_id or Plan_type or Status_id and apparently come from 4 different tables, none of which are actually defined our used in a Join of any type so you have lots of other issues to even get started.

Apparent use of character values that look like dates means this is even less likely to be a good idea with your data as 12/31/2022 will come before 2/31/2022 in default sort order for character values. So "prior" and "current" may not be what you expect.