As everyone suggested, you should use datastep instead of proc import to avoid issues. below is the code I have developed recently and you can use the same.
/* first create standard reference table */
proc sql;
create table mylib.ref_table
(IdNum char(4),
Gender char(1),
Jobcode char(3),
Salary num,
Birth num informat=date7.
format=date7.,
Hired num informat=date7.
format=date7.);
/* compare whether column names are matching or not with reference tables*/
proc sql ;
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(mylib)
and upcase(memname) = upcase('ref_table'))a
full join
(Select name
from Dictionary.columns
where upcase(libname)= upcase('work')
and upcase(memname) = upcase('test_table))b
on upcase(a.name) =upcase(b.name)
where a.name is missing or b.name is missing)
;
/* check whether order and type of column names are not matching*/
proc sql ;
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(mylib)
and upcase(memname) = upcase('ref_table'))a
inner join
(Select name, varnum, type, length
from Dictionary.columns
where upcase(libname)= upcase('work')
and upcase(memname) = upcase('test_table))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
);
/* finally reporting first step in that is capture macro variables from above queries*/
Data Temp;
Length column $500.;
If index(strip("&COLUMN_NAME_MATCHORNOMATCH" ), "ALL COLUMN")>0
then column ="&COLUMN_NAME_MATCHORNOMATCH";
else column =catx(' ', "Unmacthed columns are","&COLUMN_NAME_MATCHORNOMATCH" );
output;
If index(strip("&COLUMN_Values_MATCHorNOMATCH " ), "ALL COLUMN")>0
then column ="&COLUMN_Values_MATCHorNOMATCH ";
else column =catx(' ', "Unmacthed columns for type or length or position
are","&COLUMN_Values_MATCHorNOMATCH " );
output;
run;
/* use proc report for reporting purpose*/
Title "Final info about metadata for &syslast table";
Proc report data =temp nowd;
column column;
define column/display;
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;
/* you can check above code with example below*/
/* reference table*/
proc sql;
create table ref_paylist
(IdNum char(4),
Gender char(1),
Jobcode char(3),
Salary num,
Birth num informat=date7.
format=date7.,
Hired num informat=date7.
format=date7.
);
/*test table*/
proc sql;
create table test_paylist
(IdNum char(4),
Gender char(1),
Jobcode char(3),
Salary num,
Birth num informat=date7.
format=date7.
);
/* you just need to send macro paremeters for your test table and then everything should work*/
where upcase(libname)= upcase('work')
and upcase(memname) = upcase('test_table)
... View more
![SASGF 2020 [Virtual] Attendee](/html/@A53A708409112314CE528E074D4C8C1F/badge_icons/badge_sasgf20_attendee.png "SASGF 2020 [Virtual] Attendee"){kind=icon}
