DATA Step, Macro, Functions and more

Flaging the Incremental Data_post2

Reply
N/A
Posts: 0

Flaging the Incremental Data_post2

Dear All,

I've two datasets (OLD & NEW) to compare and create an incremental report of New and Updated records (ie., only 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).

data old;
input si x y z a b c;
cards;
1 12 23 34 45 56 67
2 78 89 90 12 23 34
3 45 56 67 78 89 90
4 09 98 87 76 65 54
5 43 32 21 09 98 87
6 76 65 54 43 32 21
;
run;

data new;
input si x y z a b c;
cards;
1 12 23 34 45 56 67
2 78 89 90 12 23 34
3 45 56 67 89 78 90
4 09 98 87 76 65 54
4 54 65 76 87 98 09
5 43 32 21 09 98 87
6 76 65 54 43 32 21
7 01 92 83 74 65 56
8 10 29 38 47 56 65
;

In the NEW dataset, I've added
2 completely new records (for variable si 7 & 8)
1 new record to an existing variable si 4.
1 record modified (data correction) to an existing variable si 3.

Hence the desired out dataset should have 4 records (only incremental data) with the flags in a new variable:

si x y z a b c flg
3 45 56 67 78 89 90 MD
4 54 65 76 87 98 09 NE
7 01 92 83 74 65 56 NN
8 10 29 38 47 56 65 NN

Awaiting your response.

Best regards,
Chris Nevin
N/A
Posts: 0

Re: Flaging the Incremental Data_post2

Posted in reply to deleted_user
proc compare like this [pre]proc compare base= {old} compare= {new} out= differences outall outnoequal ;
id si ;
run ; [/pre] creates an output dataset in which up to 4 rows are created for each ID (si) compared.
The rows on the out= data set are flagged
BASE (original data)
COMPARE (the new data)
DIF (for each numeric variables, value difference and for differing character strings, a difference variable has "X" where a character in the string is different and "." where they are the same.)
PERC percentage differences

This information is not in the structure you want, but converting the information structure will provide a good test of your learning of SAS programming.

PeterC
N/A
Posts: 0

Re: Flaging the Incremental Data_post2

Posted in reply to deleted_user
Dear Peter,

With the below PROC step, I can get the incremental data (INC) of 4 new & updated records:

proc sql;
create table INC as (select si, x, y, z, a, b, c from new
except
select si, x, y, z, a, b, c from old);
quit;

The critical part is to have flags in a new variable for these 4 records as below:

si x y z a b c flg
3 45 56 67 78 89 90 MD
4 54 65 76 87 98 09 NE
7 01 92 83 74 65 56 NN
8 10 29 38 47 56 65 NN

Could Peter/anyone please assist me.

Thank you,
Chris Nevin
Valued Guide
Posts: 2,177

Re: Flaging the Incremental Data_post2

Posted in reply to deleted_user
Chris

have you tried the code I suggested? (or followed examples in the on-line doc in particular http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/a002473397.htm ?)

PeterC
Ask a Question
Discussion stats
  • 3 replies
  • 128 views
  • 0 likes
  • 2 in conversation