one approach, this was done when comparing to excel file, this might be useful to you
/* first build a reference table/standard of what you really want
and the comparisions with your file*/
proc sql;
create table ref_paylist
(IdNum char(4), Gender char(1), Jobcode char(3), Salary num, Birth num, Hired
num informat=date7. format=date7.);
/* secondly check the column names and capture non matching name immediately after you have read external data */
proc sql noprint;
select case when name is missing then "ALL COLUMN NAMES MATCHED" else name end
into :COLUMN_NAME_MATCHORNOMATCH SEPARATED by ',' from
(select coalesce(a.name, b.name) as name, count(*) from (Select name from
Dictionary.columns where upcase(libname)=upcase('work') and
upcase(memname)=upcase('ref_paylist'))a full join
(Select name from Dictionary.columns
/* this is your file*/
where upcase(libname)=upcase('work') and
upcase(memname)=upcase('test_paylist1'))b on upcase(a.name)=upcase(b.name)
where a.name is missing or b.name is missing);
/* compare the columns length and other things
you can change to whatever you want*/
proc sql noprint;
select case when name is missing then
"ALL COLUMN Type position length MATCHED" else name end
into :COLUMN_Values_MATCHorNOMATCH SEPARATED by ',' from
(select coalesce(a.name, b.name) as name, count(*) from (Select name, varnum,
type, length from Dictionary.columns where upcase(libname)=upcase('work') and
upcase(memname)=upcase('ref_paylist'))a inner join
(Select name, varnum, type, length from Dictionary.columns where
upcase(libname)=upcase('work') and upcase(memname)=upcase('test_paylist1'))b
on upcase(a.name)=upcase(b.name) where a.varnum ne b.varnum or a.type ne
b.type or a.length ne b.length);
/* capture the above data in table*/
Data Temp;
Length column $500.;
If index(strip("&COLUMN_NAME_MATCHORNOMATCH"), "ALL COLUMN")>0 then
column="&COLUMN_NAME_MATCHORNOMATCH";
else
column=catx(' ', "Unmatched columns is/are", "&COLUMN_NAME_MATCHORNOMATCH");
output;
If index(strip("&COLUMN_Values_MATCHorNOMATCH "), "ALL COLUMN")>0 then
column="&COLUMN_Values_MATCHorNOMATCH ";
else
column=catx(' ', "Unmatched columns for type or length or position
is/are", "&COLUMN_Values_MATCHorNOMATCH ");
output;
run;
/* do reporting */
Title "Final info about metadata for test_paylist1 table";
Proc report data=temp nowd;
column column;
define column/display "Column";
compute column;
if index(column, "ALL COLUMN")=0 then
call define
(_col_, "style", "style={background = red}");
else
call define
(_col_, "style", "style={background = green}");
endcomp;
run;
... View more
![SASGF 2020 [Virtual] Attendee](/html/@A53A708409112314CE528E074D4C8C1F/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
