Using PROC DQSCHEME Part 3: How to CONVERT custom schemes

[#1]

One feature of the DQSCHEME procedure is that schemes can be produced in one of two formats: SAS or QKB. While the QKB format is necessary for adding schemes to custom QKBs and working with schemes in SAS Data Quality software, QKB scheme files cannot be viewed or edited programmatically. If there are errors in the scheme, you might not find them until you've already applied your scheme and gotten unexpected results.

The CONVERT statement, which is used to switch the format type of a scheme, provides a helpful workaround. In this blog, I'll clean up our faulty QKB scheme from the previous blogs in this series. I’ll use the CONVERT statement to convert a QKB scheme to SAS format, which will allow me to edit the scheme programmatically. Once I’m satisfied with my scheme, I’ll convert it back to QKB format for future use. If you haven’t already, check out my previous posts about the CREATE statement and the APPLY statement. Using the CONVERT statement The DQSCHEME procedure CONVERT statement is used to change the format of a scheme file. We’ve seen previously that schemes can be created in either SAS format (stored in SAS tables) or QKB format (stored in QKB scheme files). The CONVERT statement enables you to easily convert a scheme between SAS and QKB formats – you only need to provide the conversion type, the existing scheme name, and the converted output scheme name. There are no optional arguments for this statement. CONVERT a QKB scheme file to SAS format I mentioned the CONVERT statement as a scheme editing solution in my last blog after reviewing the undesired results from applying the cars QKB scheme. We can’t edit a QKB scheme file programmatically, but we can convert a QKB scheme to SAS format and then edit it programmatically like I did in part 1. First, I’ll convert the cars.sch.qkb scheme file to SAS format. In the CONVERT statement, I’ll include the 3 required arguments: QKBTOSAS as my conversion type, the cars fileref as my existing scheme, and cars_QKBtoSAS as my converted scheme name.

filename cars "/home/student/cars.sch.qkb";
proc dqscheme;
convert QKBTOSAS in=cars out=cars_QKBtoSAS;
run;

In the log, I see a message about the meta options for the new scheme cars_QKBtoSAS. These are the same meta options stored in cars.sch.qkb.

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

When viewing cars_QKBtoSAS, I see that the scheme has been converted successfully. This reveals the problems with the scheme, which are the same problems with our default SAS scheme from part 1: missing data for any versions of ‘Toyota Corolla” and inconsistency in final values.

I can view the table properties to confirm that the metadata options were stored successfully. Recall that metadata options are stored in data set labels.

Now that my scheme is in SAS format, I can make my desired changes. As a reminder, you can use any SAS method to update the format data set if you make sure to preserve the metadata options in the data set label. I’ll use PROC SQL.

proc sql;
create table cars_QKBtoSAS_edit(label='"EX" "P" "" ""') as
select data, propcase(standard) as STANDARD
from cars_QKBtoSAS;
insert into cars_QKBtoSAS_edit
values('Toy. Corolla', 'Toyota Corolla')
values('TOYOTA Corolla', 'Toyota Corolla');
quit;

My resulting scheme data set, cars_QKBtoSAS_edit, looks much better with the applied changes.

CONVERT a SAS scheme to QKB scheme file format

Now that we’re done, I can convert the edited scheme back to QKB format so that it’s ready for future use. This time, I’ll use SASTOQKB for the conversion type.

filename cars_fin "/home/student/cars_final.sch.qkb";
proc dqscheme;
convert SASTOQKB in=cars_QKBtoSAS_edit out=cars_fin;
run;

The log confirms that the scheme was successfully converted to QKB format and stored in cars_final.sch.qkb with the appropriate metadata options.

Considerations for converting scheme formats In this post, I converted a scheme from QKB format to SAS format and then back to QKB format at the end. This is because I previously created a QKB scheme but I couldn't view or edit it programmatically, so I didn't find the problems in the scheme until I applied it in part 2. While this solution works, it's redundant when creating new custom schemes. A more efficient method is to create a SAS scheme first, then convert it to a QKB scheme once you have made any desired changes. As mentioned in previous posts, you can use SAS Data Management Studio or the SAS QKB Definition Editor to create, edit, and apply QKB schemes. These tools provide a point-and-click alternative to the method shown in this blog while also eliminating the need for scheme format conversion. Summary The CONVERT statement enables you to change a scheme’s format quickly and easily. This simple statement can be helpful for editing QKB schemes or preparing SAS schemes for use in SAS Data Quality software. For more information on the CONVERT statement, review the documentation. This post concludes my series on the DQSCHEME procedure statements. If you haven’t already, read part 1 on the CREATE statement and part 2 on the APPLY statement. Interested in learning more about SAS Data Quality programming tools and techniques? Check out the series Coding for Data Quality.  

Find more articles from SAS Global Enablement and Learning here.