Dynamic Data Quality Job to handle any type of Source File

[avvy]

Dear Users,

I have a business scenario, where I will have to process lot of tables from source storage (files are in form of CSV) for standardization i.e. Demographic standardization etc.

The DQ job has to understand the Columns and then pick the column which has demographic values (identification) and further standardize those columns.  

The challenge are as below:

1. The job has to identify the number of files in the folder location to be processed and the file names

2. The job has to identify what columns are there in individual tables and which columns refer to demographic values

3. Standardize the identified columns

4. Loop the entire process.

The major challenge is that all the files in the source can have any number of columns and can consist of different columns as compared to the previous or next file in the source folder.

Any ideas on this will really be helpful.

Thanks & Regards,

Abhishek Pathak  

[SASKiwi]

In my experience, the challenge you have set yourself is problematic if not impossible.

For starters, there is no such thing as a generic standardization process that can cater for all demographics. For example, standardizing street addresses is very different to standardizing phone numbers. How will you know what standardization method to apply without intervention? Also standardization usually requires fine-tuning the SAS QKB (Quality Knowledge Base). That also requires manual intervention.

Having said that, making use of Base SAS to prepare and post-process SAS DQ data is very worthwhile. And since you can call a SAS DQ job from Base SAS, you can fully automate processing of different versions of files with the same columns. Each file layout would need its own SAS DQ job.