Using PROC COMPARE with Case Insensitivity

3 Likes

While teaching a SAS programming course recently, a customer asked how she could ignore casing while comparing two data sets using PROC COMPARE. After further investigation, I found that while PROC COMPARE includes various options to customize the comparison, there are no specific options within the procedure to make the comparison case insensitive. This blog post will guide you on how to use PROC COMPARE with case insensitivity.

Understanding PROC COMPARE

PROC COMPARE is a SAS procedure that allows you to compare two data sets to identify any differences between them. It provides detailed reports on the discrepancies between the data sets, including differences in data values, variable attributes, and data set attributes. For example, let’s say a customer wants to compare HOA membership records from 2022 to 2023. In that year, Emily Darwin’s phone number changed, Taylor Merwood left the HOA, and Kathy Estelle joined. Below are the membership records for both years:

DATA HOA_BOARD2022;
   input First $ Last $ PhoneNumber $;
   datalines;
Nicholas McVey 555-1234
Emily Darwin 555-1325
Taylor Merwood 555-3923
;

HOA Membership 2022

sb_1_2024-04-01_11-10-46.png

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

DATA HOA_BOARD2023;
   input First $ Last $ PhoneNumber $;
   datalines;
Nicholas McVey 555-1234
Emily Darwin 555-5678
Kathy Estelle 555-9012
;

HOA Membership 2023

sb_2_HOA2023-1.png

Running PROC COMPARE, SAS tells us when each data set was created and modified, and the number of variables, observations, and variables in common.

proc compare base = HOA_BOARD2022 compare = HOA_BOARD2023;
run;

sb_3_table1-1.png

The next table gives a more detailed summary of the comparison, including the number of equal and unequal observations, and which variables have unequal values.

sb_4_table2.png

The final report outlines which observations were unequal and their values in each data set. In this case, we see observation 3 has different values for First, Last, and PhoneNumber while observation 2 has different values for PhoneNumber in each data set.

sb_5_table3.png

Case Sensitivity in PROC COMPARE

PROC COMPARE is case-sensitive. This means that it treats the same characters in different cases as different values. For example, abc and ABC would be considered different values. This can be problematic when comparing data sets where case does not matter.

For example, let’s say the HOA membership record data for 2024 changed the casing of Nicholas’ last name from McVey to Mcvey. Running PROC COMPARE on the 2023 vs 2024 data, SAS interprets McVey and Mcvey as unequal values.

HOA Membership 2023

sb_6_McVey.png

DATA HOA_BOARD2024;
   input First $ Last $ PhoneNumber $;
   datalines;
Nicholas Mcvey 555-1234
Emily Darwin 555-5568
Kathy Estelle 555-9012
;

HOA Membership 2024

sb_7_Mcvey2-1.png

PROC COMPARE Output

sb_8_uneq.png

sb_9_uneq2.png

Overcoming Case Sensitivity

Column-Specific

In order to ignore casing in PROC COMPARE, we can copy the original data sets and convert the Last column to all uppercase using the UPCASE function, then run PROC COMPARE.

data COPY_HOA2023;
	set HOA_BOARD2023;
Last = UPCASE(Last);
run;

HOA Membership 2023

sb_11_allcaps.png

data COPY_HOA2024;
	set HOA_BOARD2024;
Last = UPCASE(Last);
run;

HOA Membership 2024

sb_11_allcaps.png

proc compare base = copy_HOA2023 compare = copy_HOA2024;
run;

sb_12_equal.png

All Character Columns

There may be a scenario where we want all character columns converted to uppercase. In this example, we create an array of character variables and use a DO loop to convert each variable in the array to uppercase using the UPCASE function.

data copy2_HOA2023;
set HOA_BOARD2023;
array char[*] _character_;
do i = 1 to dim(char);
    char[i] = upcase(char[i]);
end;
drop i;
run;

HOA Membership 2023

sb_13_caps.png

data copy2_HOA2024;
set HOA_BOARD2024;
array char[*] _character_;
do i = 1 to dim(char);
    char[i] = upcase(char[i]);
end;
drop i;
run;

HOA Membership 2024

sb_14_caps.png

Conclusion

While PROC COMPARE is a powerful tool for comparing data sets in SAS, its case sensitivity can lead to unexpected results. By converting all character columns to the same case before running PROC COMPARE, you can ensure that your comparisons are not affected by case differences. This allows you to accurately identify any discrepancies between your data sets, regardless of the case of the character values.

For more information on PROC COMPARE, visit SAS Help Center: Overview: PROC COMPARE.

Quentin

Great post. PROC COMPARE is one of my favorite procs! Only suggestion for a small improvement to consider would be to consider creating the second dataset where everything is upcased as view, instead of a new dataset. If the data is big, sometimes views can be more efficient as they don't require storage space.

Also in a post like this where a PROC COMPARE is the point, introducing the idea of views could be a distraction for readers unfamiliar with the concept.

SASKiwi

IMHO PROC COMPARE is such a useful tool and is one of my most frequently used ones. It's invaluable for developers to check the impact of process changes, but also for administrators to confirm if data is identical across SAS environments.

It could definitely do with a bit of love though, as its reporting capabilities still seem to be stuck in pre-ODS days.

Ksharp

There is a more simple way to transform all the character in a table into UPPER CASE.

Take table sashelp.heart as an example:

filename x "%sysfunc(pathname(work))\temp.xpt";
proc cport library=sashelp file=x outtype=upcase;
select heart;
run;

proc cimport infile=x library=work;
run;