topic Re: SAS and Data Quality Dimensions in SAS Programming

SAS and Data Quality Dimensions

LineMoon — Sat, 26 Oct 2019 13:22:06 GMT

Hello Experts,

Please, I would like to know, if you have created some sas programs and templates for the data quality Dimensions : COMPLETENESS,UNIQUENESS,TIMELINESS,VALIDITY,ACCURACY,CONSISTENCY

In order to enhance the data quality reports ?

Are there any SAS implementions for the data quality dimensions as specified in the standard : BCBS 239 ?

Re: SAS and Data Quality Dimensions

PaigeMiller — Sat, 26 Oct 2019 13:28:41 GMT

Internet searches for

SAS BCBS 239

finds lots of hits

Re: SAS and Data Quality Dimensions

LineMoon — Sat, 26 Oct 2019 18:53:10 GMT

Thank very much for your answer.

As a part of the creation of the six data quality Dimensions (COMPLETENESS, UNIQUENESS, TIMELINESS, VALIDITY, ACCURACY, CONSISTENCY) via SAS, there are three components that will be involved in the process of the generation of the Data Quality Report:

Input Design
SAS Program
Output (Data quality Report)

It seems clear that, there is a strong dependency between the Input Design and SAS program (to run with less coding and keeping more transparency). In order to create by SAS an automatic Data Quality report.

Consequently, are there any general solution that have been proposed for both the Input Design and SAS program?

Re: SAS and Data Quality Dimensions

Reeza — Sun, 27 Oct 2019 03:22:55 GMT

For starters are you referring to a specific product, such as SAS Data Quality Studio or Data Integration Studio? Or are you referring to using Base SAS to do data quality management? Given your last question, I'm assuming the latter and my further comments are based on this assumption.

There is a dedicated SAS product designed to characterize/profile data quality measures. In general though, there needs to be some subject matter expertise applied, because you need to specify both the data and the rules and you need to develop a system to do so. I've had to this before by the way, building a data quality report and system to transfer data to different government entities to meet their rules. Without Data Integration studio sadly...didn't know enough at the time to have used that instead anyways!

Re: SAS and Data Quality Dimensions

LineMoon — Sun, 27 Oct 2019 17:47:25 GMT

Thank you very much.

I am referring to the use of the Base SAS to do data quality report with the six data quality Dimensions: COMPLETENESS, UNIQUENESS, TIMELINESS, VALIDITY, ACCURACY, CONSISTENCY

Re: SAS and Data Quality Dimensions

Reeza — Sun, 27 Oct 2019 21:24:22 GMT

Ok, so yeah, you need to define your rules and write your programs. They will vary based on input. For example, for some of our data the timeliness is 24 hours and for others its 30 days. You can only know that by knowing the business rules.

So pick a data system and start there by first establishing the business rules around how data are collected. You then translate that to the dimensions above, ie completeness for 'name' type fields would be mean that First Name and LastName are always filled out but MiddleName can be blank.
Uniqueness means that each transaction ID should be unique for example - this may not be true in all systems, especially if you have a transaction system that allows for returns. In that case, the TransactionID and Type (purchase or return) form the unique key to be checked.

I don't recall the definitions of the other terms at the top of my head but hopefully this helps you get started.

This paper walks through some of it and you can also search on lexjansen.com for more examples:
https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/093-29.pdf

Note that is not a trivial task, but can be done, it just takes some time.

There's also a SAS code book that can help you get there faster, the macro is in the library.

https://communities.sas.com/t5/SAS-Communities-Library/Library-Datasets-Summary-Macro-DATA-SPECS/ta-p/544757

You can probably use that to help build your rules.

Re: SAS and Data Quality Dimensions

LineMoon — Mon, 28 Oct 2019 21:55:21 GMT

Thank you very much for these useful and details information.

That’s very kind from you.

AS you said ““that is not a trivial task, but can be done, it just takes some time”

That’s right; and, in order to find a general solution, the key could be in the “input Design” that will be running in SAS Program.

As you know, the input design implemented the business rules and easily understood by the functional and Technical; and loaded with less complexity in SAS.

Therefore, in many cases, the inputs design, it is combination of three components:

Set of fields to apply the data quality
Data Quality Dimensions to apply: COMPLETENESS, UNIQUENESS, TIMELINESS, VALIDITY, ACCURACY, CONSISTENCY
Set of reference variables used in the compare.

The appearance of the three components in the “input design” in a smart way could solve the issue

Re: SAS and Data Quality Dimensions

Reeza — Tue, 29 Oct 2019 15:25:45 GMT

Yes, but that also takes time and you need to know the rules that could even come into play. My DQ programs for clinical trials and health care were different from the data quality rules when I was n agriculture. But similar idea.

When I built similar systems I put the rules/logic in an Excel file and had the business drive the rules and I just implemented them.

Thinking about all of the different possible cases isn't trivial, which is why there's a dedicated SAS product that does this, SAS Data Quality Studio.

Re: SAS and Data Quality Dimensions

LineMoon — Tue, 29 Oct 2019 23:43:03 GMT

As for input , That's right . Instead, the Output design could have nearly the same format ?

Re: SAS and Data Quality Dimensions

Reeza — Wed, 30 Oct 2019 01:19:35 GMT

Sure it could.

Re: SAS and Data Quality Dimensions

LineMoon — Fri, 01 Nov 2019 08:42:23 GMT

In order to apply the 6 Data quality dimensions on N variables(for exemple : 1000 variables),

How much time do you estimate to do this job?

Is there any method for estimating the time necessary to do this job ?

Re: SAS and Data Quality Dimensions

LineMoon — Fri, 01 Nov 2019 21:25:27 GMT

@PaigeMiller : I think, you mean this link https://blogs.sas.com/content/sascom/tag/bcbs-239/

I have read these 14 principles, I am interested by their implementation in SAS.

Thereby, could the scope of the data quality for the Banking credit risk data, defined by the banking supervisory, "became" the same as “CDEs=Critical Data elements” ? in order to limit the scope of the data quality.

Re: SAS and Data Quality Dimensions

Reeza — Sat, 02 Nov 2019 17:41:38 GMT

Depends on the variables. If 25 are diagnosis codes for example, you can look at them all together, and there's probably similar functionality in banking data. For example, all amount fields can be looked at together.

I would say you would first go through and group variables together and identify which need to checked and which ones don't and which go together. Then you have the subset you need to check. I'd guess you're looking at 20/30 minutes per variable here to make decisions and ensure things are correct and not accounting for extra follow up time. Some variables may take a minute to check and verify and some, like an account type that's changed over time and has old and new types may take a few hours so 20/30 minutes on average. Usually you assume 5 productive hours a day. You can do the math from there.

Re: SAS and Data Quality Dimensions

LineMoon — Sat, 02 Nov 2019 23:13:05 GMT

Thank you very much.

It's a good idea