topic Re: Flaging the Incremental Data_post2 in SAS Programming

Flaging the Incremental Data_post2

deleted_user — Fri, 27 Feb 2009 12:18:28 GMT

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 & 😎
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

Re: Flaging the Incremental Data_post2

deleted_user — Fri, 27 Feb 2009 14:31:38 GMT

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

Re: Flaging the Incremental Data_post2

deleted_user — Mon, 02 Mar 2009 07:42:42 GMT

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

Re: Flaging the Incremental Data_post2

Peter_C — Wed, 04 Mar 2009 15:04:46 GMT

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