Dears,
As part of SCDTYPE2 implementation, we requried to update and delete the records.
For instance given attachement, :
Need to delete highlight one , please can you help on this request.
Thanks
I don't see any attachment?
sorry jst attached. Thanks
Dears,
As part of SCDTYPE2 implementation, we requried to update and delete the records.
For instance given attachement, :
id |
name |
age |
sex |
O_obs |
C_obs |
DFINOBS |
1 |
John |
10 |
M |
01Feb2019 |
01Jan2019 |
31Jan2019 |
1 |
John |
10 |
M |
01Feb2019 |
01Feb2019 |
31Dec9999 |
2 |
Mary |
10 |
F |
01Feb2018 |
01Jan2018 |
31Jan2018 |
2 |
Mary |
10 |
F |
01Feb2018 |
01Feb2018 |
31Dec9999 |
2 |
Mary |
10 |
F |
01Mar2018 |
01Feb2018 |
28Feb2018 |
2 |
Mary |
10 |
F |
01Mar2018 |
01Mar2018 |
31Dec9999 |
Need to delete highlight one , please can you help on this request.
Thanks
What is logic behind highlighting this obs? And what do you mean by 'highlight'? Is this for reporting purposes?
Please be more specific
The highlighting obs should be deleted.
1-Feb-18 | 1-Feb-18 | 31-Dec-99 |
because we the same ID updated in 1-Mar -18.
id | name | age | sex | O_obs | C_obs | DFINOBS |
1 | John | 10 | M | 1-Feb-19 | 1-Jan-19 | 31-Jan-19 |
1 | John | 10 | M | 1-Feb-19 | 1-Feb-19 | 31-Dec-99 |
2 | Mary | 10 | F | 1-Feb-18 | 1-Jan-18 | 31-Jan-18 |
2 | Mary | 10 | F | 1-Feb-18 | 1-Feb-18 | 31-Dec-99 |
2 | Mary | 10 | F | 1-Mar-18 | 1-Feb-18 | 28-Feb-18 |
2 | Mary | 10 | F | 1-Mar-18 | 1-Mar-18 | 31-Dec-99 |
@dhruvakumar wrote:
The highlighting obs should be deleted.
1-Feb-18 1-Feb-18 31-Dec-99
because we the same ID updated in 1-Mar -18.
id name age sex O_obs C_obs DFINOBS 1 John 10 M 1-Feb-19 1-Jan-19 31-Jan-19 1 John 10 M 1-Feb-19 1-Feb-19 31-Dec-99 2 Mary 10 F 1-Feb-18 1-Jan-18 31-Jan-18 2 Mary 10 F 1-Feb-18 1-Feb-18 31-Dec-99 2 Mary 10 F 1-Mar-18 1-Feb-18 28-Feb-18 2 Mary 10 F 1-Mar-18 1-Mar-18 31-Dec-99
And what is the logical rule for that?
With SCD2 you NEVER delete a record but you expire it.
Assuming ID is your generated key and Name is your business key: I can't even see a reason in the sample data you've posted why there should be two records. All the other columns which potentially could be under change tracking are the same. With SCD2 there should be only a single row in such a case.
With SCD2 you need to have either a VERSION column and for every single row with a change to columns under change tracking for the same business key you add a new version - or you have start and end date columns and if you add a new row (an update) you also change the end date of the previous version to the start date of the new version (or current start date -1 day if you wish).
So: You first need to fix your table and clearly define the cases for your load logic (new business key, change for existing business key, expiration of current record for a business key, reopening an previously expired record for a business key) before even asking HOW to technically implement things.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.