topic Re: Data profiling by referencing the patterns and datatype from a lookup metadata table in dataflux in SAS Data Management

Data profiling by referencing the patterns and datatype from a lookup metadata table in dataflux?

ShikhaAgarwal — Mon, 18 May 2020 08:12:55 GMT

So the requirement is not just profiling and exploring the data but to be able to highlight the incorrect values by referencing a metadata table which has pattern , datatype, length everything mentioned for each field.

I could find any way to do it in dataflux other than writing my own SAS codes.

Let me know the feasibility please'.

Re: Data profiling by referencing the patterns and datatype from a lookup metadata table in dataflux

RonAgresta — Wed, 20 May 2020 11:23:15 GMT

Have you tried to author custom metrics and/or business rules and add these to the profile job? Check out the documentation here. The expression language that sits behind the business rule builder is very flexible and it give you some options to meet your use case needs.

Ron

Re: Data profiling by referencing the patterns and datatype from a lookup metadata table in dataflux

ShikhaAgarwal — Thu, 21 May 2020 03:12:34 GMT

Hi RonAgresta,

Thanks for reverting. I checked the mentioned business rule functionality but i failed to understand how SAS pattern outcomes can be compared to the patterns i have.

Like if i have a table with fields name and the pattern they should follow.

Example:

Fieldname,format

Address Australian postcode,{NNNN}

where N is a number

Now i fail to understand how to do the profiling referening this table. I want to highlight those records in the incoming csv files which do not follow the above pattern. Now the roadblocks are:

1)SAS profiling patterns analysis gives outcomes in A and 9 which will not follow the rules which i will create on the business rule manager

2) if in data job i can run some sas codes to bring these pattern annotations in one version , i can do some comparisons and create flags.

3) I could not find how to profile date fields to understand whether its DDMMYY or MMDDYY etc format. I have to give everything in string to get the pattern analysis outcomes. But then it give 99/99/99 . How does it help if i need to parse and create more rules to identify the data format?

Appreciate more help in these please.

Regards,

Shikha

Re: Data profiling by referencing the patterns and datatype from a lookup metadata table in dataflux

VincentRejany — Mon, 25 May 2020 16:44:45 GMT

Hi Shikha

Within SAS DataFlux Data Management Studio, you can create a custom metric to be used within a profile job. Typically for your SSN

You can also use regular expressions for checking dates.