Flaging the Incremental Data

[deleted_user]

Dear SAS,
I've two datasets (OLD & NEW) to compare and create an incremental report of New and Updated records (ie., difference between the two data). Also need to flag those records in a new variable as noted below.
New or Updated data can be:
1) a new record added where the subject is NEW (should be flaged as NN).
2) a new record added to an existing subject (flaged as NE).
3) a record modified (data correction) to an existing subject (MD).

OLD file (plz copy and paste to .XLS and create a SAS dataset. First row includes the variables)

SID1A SOC_ABBR PT_TXT AEVNAM1A AEVSTT1D AEVEND1D AEVCTU1C AEVSEV1C
1_00001 Musc Arthralgia PAIN IN THE ELBOW 30-Jan-2008 3-Mar-2008 0 1
1_00003 Eye Visual acuity reduced SUBJECTIVE DESCREASE VISUAL ACUITY 28-Mar-2007 28-Jan-2008 0 1
1_00004 Infec Urinary tract infection URINARY INFECTION 12-Apr-2007 13-Apr-2007 0 1
1_00006 Infec Nasopharyngitis COLD 19-Mar-2007 7-Apr-2007 0 1
1_00007 Infec Influenza FLU 9-Mar-2007 23-Apr-2007 0 2
1_00008 Nerv Amnesia MEMORY LOSE 15-May-2008 . 1
1_00009 Gastr Toothache TOOTHACHE 1-Jul-2007 1-Jul-2007 0 1
1_00010 Skin Skin lesion PSORIASIS VS ECZEMA 5-May-2007 5-May-2007 1 1


NEW file (plz copy and paste to .XLS and create a SAS dataset. First row includes the variables)

SID1A SOC_ABBR PT_TXT AEVNAM1A AEVSTT1D AEVEND1D AEVCTU1C AEVSEV1C
1_00001 Musc Arthralgia PAIN IN THE ELBOW 30-Jan-2008 3-Mar-2008 0 1
1_00003 Eye Visual acuity reduced SUBJECTIVE DESCREASE VISUAL ACUITY 28-Mar-2007 28-Jan-2008 0 1
1_00004 Infec Urinary tract infection URINARY INFECTION 12-Apr-2007 13-Apr-2007 0 1
1_00006 Infec Nasopharyngitis COLD 19-Mar-2007 7-Apr-2007 0 1
1_00007 Infec Influenza FLU 9-Mar-2007 23-Apr-2007 0 2
1_00008 Nerv Amnesia MEMORY LOSE 15-May-2008 22-Aug-2007 . 1
1_00009 Gastr Toothache TOOTHACHE 1-Jul-2007 1-Jul-2007 0 1
1_00009 Gastr Vomiting VOMITING 1-Apr-2007 6-Apr-2007 0 2
1_00010 Skin Skin lesion PSORIASIS VS ECZEMA 5-May-2007 5-May-2007 1 1
1_00011 Inj&P Thermal burn BURN 25-Aug-2007 15-Sep-2007 0 1
1_00012 Infec Upper respiratory tract infection UPPER RESPIRATORY INFECTION 11-Jan-2008 20-Jan-2008 0 1

In the NEW file, I've added
2 new records where the subjects are NEW (1_00011 & 1_00012)
1 new record to an existing subject (1_00009).
1 record modified (data correction) to an existing subject (1_00008).

Hence the incremental data should have only 4 records with the following flags in a new variable:
2 NN, 1NE, 1MD

Awaiting your response.

Best regards,
Chris Nevin

[sbb]

I will offer you some suggestions for SAS coding - surely you are looking for guidance and not just someone to do your work, right?

One approach is code up a DATA step to read the two inputs, creating SAS files, then sort the data, and use PROC COMPARE to generate an output with differences, and you should have the desired results, I would say.

The SAS product documentation is hosted at the SAS support website http://support.sas.com/ and you will also find many additional technical papers on various SAS programming topics at the site.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Dear Scott,

Thank you very much for your prompt response but I couldn't get my desired output.

I simplified my query and posting it as a seperate Thread.

Best regards,
Chris Nevin