BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
deleted_user
Not applicable
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
deleted_user
Not applicable
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
Peter_C
Rhodochrosite | Level 12
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 691 views
  • 0 likes
  • 2 in conversation