How to flag new or changed data using SAS

[yahoo0806]

Hi all,

I'm having a CDISC standard AE data and I want to compare the new and old AE data (different data cut date) to flag any new or changed AE records. 

I also want to color code the new record (highlight in a different color for the whole row) and the changed record (only highlight the differences in the cell).

I assume this will involve using proc compare and proc report. However, about the color coding the record, I'm not quite sure how to do it. Can anyone help?

Many thanks!

[Patrick]

@yahoo0806 

You've got here two very separate questions:

1. How to implement code logic to work out differences between two cuts of data

2. How to create a report with conditional formatting.

For 1:

Please provide some sample data in the form of working SAS data steps. 

What about deletes (row earlier data but no more in current data).

For 2:

What the output format? Excel, HTML, PDF, ....?

[yahoo0806]

Hi Patrick,

I think you are right. 

so for the 1st point, i think my current logic is to combine the two datasets using the code like below:

data all; length source $10;
set new (in=a) old(in=b);
if a then source = 'NEW';
if b then source = 'OLD';
by col2 col9 col10 col11 col16 col17 col19 col20 col23;
run;

So after i put the new and old data together, it is easy for me to identify the new records (which is only in new data) and the 'deleted' records (which is only in old data). (the green highlighted column in the excel file).

My problem now is to identify the changed data (the yellow highlighted cells). Here i not only want to create a flag to flag the changed record, i also want to identify which value in which variable has changed. Remember, this is a dataset which has 29 variables in it. 

attached is an example data, in total i have col 1 to col29, here i only listed part of the columns.

Hope it is helpful.

Many thanks!

[yahoo0806]

Just one more thing to add, the changed data can appear any where in these 29 columns (col1 to col29). So the code has to be universal enough to capture them all .......

[Patrick]

@yahoo0806 

In the data step you've posted old values will overwrite new values for matches.

How do you intend to capture which column(s) changed? A new table with a row per change? A string of concatenated variable names?

The name of the changed variable only or also the value change (old/new)? 

Depending on what you actually need this for Proc Compare does eventually what you're after.

[yahoo0806]

Hi @Patrick 

I thought the set statement will add the dataset together instead of overwrite each other. And i also checked the log, the total number of records is the sum of the records in the two datasets. So would you mind let me know which code is having the problem of what said below?

For what i want to achieve, i have created a new tab in the attached dataset. i was also think about proc compare. to ask SAS output the the difference data to make the changes, however, i'm not sure how to exactly write the SAS cod. Can you please suggest?

Many thanks!!

[Patrick]

@yahoo0806 

Yes, you are right. You are not overwriting the data. You are using a SET but I have "seen" a MERGE statement and what I wrote would have been for MERGE. 

I still don't understand how your desired result should look like. Do you want to use SAS to create an Excel report with cell highlighting as attached? Proc Compare wouldn't do that for you. It potentially could help you to find changed records and variables but you would still need to write the report.

As for a code sample: Have you already consulted the documentation? There are quite a few examples for Proc Compare which could give you a start.