topic validation summary report in SAS Programming

validation summary report

padmanabh — Thu, 23 Jul 2020 01:14:33 GMT

Hi,

I want to create a validation summary report for a table or a csv file. I have a sql table with x columns and the x columns has y no of records. The validations include lookups,missing values, duplicates and invalid values.

e.g. name1

numeric1

date1

name2

The data I have taken from sql which is imported using the proc import data= option.

Now, I would like to have a report with each column against which the number of invalid records. On top of the report the overall no of records provided and the table name or csv filename.

I am using the sas university edition to do this.

Could anyone please guide on how do I do this, I am pretty new to sas and can do the basics

Re: validation summary report

smantha — Thu, 23 Jul 2020 04:24:49 GMT

There is no single step that will give you this answer. You have to define what lookups and invalids mean. Other wise the closest that comes to the description you have is

proc summary data=inputdata  missing no print;
class _character_;
output out=summary(drop=_:) n= nmiss= max= min=/autoname;
run;

proc summary data=inputdata  missing no print;
class _numeric_;
output out=summary(drop=_:) n= nmiss= max= min=/autoname;
run;

However the output itself can be little confusing. IF you are familiar with macros you can do the above for each variable by placing them in a macro loop.

Re: validation summary report

andreas_lds — Thu, 23 Jul 2020 05:13:13 GMT

Please post example data in usable form and show what you expect as output.

Re: validation summary report

padmanabh — Tue, 28 Jul 2020 05:55:36 GMT

Table_Name	Cust_Name
No of Records	200


Columns	Total_Records	Missing_Values	Duplicates	Invalid_Values	Lookups
Name_character	200	0	3	1	2
Age_numeric	199	1	50	3