Updating new record in scd type 2 history using proc sql

[MG18]

hi all, 

 i want to update the old record in scd type 2 history using proc sql  and how can i do it ?

below is the table screen shot :- 

select PARTY_KEY,PARTY_number,CHANGE_END_DATE,CHANGE_begin_DATE,SOL_id
from AMLCORE.fsc_party_dim_solnull where party_number='00077886' ;

Output :- 

PARTY_KEY	PARTY_NUMBER	CHANGE_END_DATE	CHANGE_BEGIN_DATE	SOL_ID
14625	00077886	01/01/5999 00:00:00	01/16/2019 00:00:00	NULL
1267882	00077886	01/16/2019 00:00:00	02/13/2014 00:00:00	012

I want to update the SOL_ID from old record to new record .Please help me .

i have tried to run below query but it is failing :- 

update AMLCORE.fsc_party_dim_solnull new set sol_id= (select sol_id from AMLCORE.fsc_party_dim_solnull old
where old.party_number=new.party_number and old.segment_id=new.segment_id and CHANGE_END_DATE<>'01-jan-5999' )
where CHANGE_END_DATE='01-jan-5999' and sol_id is null and CHANGE_BEGIN_DATE >'01-jan-2019' ;

[ballardw]

You have several issues. One is that the values you show for CHANGE_END_DATE and CHANGE_START_DATE appear to be datetime values but since the appearance you show is not a standard SAS datetime format I suspect the values are character. Therefore to compare for equality you must use the full value of the string not:

CHANGE_END_DATE='01-jan-5999'

You would have to use

CHANGE_END_DATE='01/01/5999 00:00:00'

exactly as it appears in the table. Same for the other comparison values

second is that <> is not the same as "not equal" in SAS. If you want "not equal" use one of NE ^= ~=

Third is that > or < are VERY unreliable when used with character values. '01 apr 1999' is "less than" '01 jan 1999'.

You should copy the entire procedure call and messages from the LOG and paste that into a code box opened with the forum's {I} menu icon to provide the code and error messages preserving appearance as in the log. The main message windows will reformat text moving some of the indicators SAS supplies in the log to show where errors occur.

If you really want to use range comparisons with dates, times or datetime values it is strongly recommended that you use actual SAS date, time and datetime values. There are a large number of functions to manipulate such and then comparisons such as > or < will make sense. Literal dates and times would have to be coded a bit differently to indicate such. With a datetime value that you want to compare to a date you could use:

datepart(datetimevariable) = '01JAN1999'd;

The date portion must be in the ddMONyy or ddMONYYYY layout and the D immediately following the quoted string tells SAS you want to use the SAS date value. Similar rules for datetime and time literals.