Dear Tom,
Thanks a lot for your help. Sorry for my late reply. I tried your code, it works for using the sample data(See the attached file, the variable names are "name, sex, age, height, and weight"). The thing is that I am not able to replace the sample data with my data. In the following code, I named the input table as "out_adc20141" and the reference table as "out_adc20134". However, no matter what I call them, the code will compare the sample data instead of my data.
I am asking a very stupid question here, but I really want to know. In you code, I did not find a place to mention where the data comes from. How could SAS know that you want to compare the sample data? Thanks again.
%macro check_reference_data
/* Macro to check attributes of input data against reference */
/* data, and create a ranked list of data integrity exceptions */
(out_adc20141 /* name of input table to compare */
,out_adc20134 /* name of reference table for comparison */
,out_result /* new data set to hold result */
);
proc sql;
%* Create VIEWs to allow use of dataset options ;
create view work._tmpin as select * from &out_adc20141;
create view work._tmpref as select * from &out_adc20134;
create table &out_result. as
select "&out_adc20141." as DATASET
, "&out_adc20134." as REFERENCE
, a.name as ref_name
, b.name
, a.length as ref_length
, b.length
, a.type as ref_type
, b.type
, a.format as ref_format
, b.format
, case
when b.name is missing then
'SEV-1: MISSING COLUMN'
when a.name is missing then
'SEV-4: EXTRA COLUMN'
when b.type ne a.type then
'SEV-1: MISMATCHED TYPE'
when b.length > a.length then
'SEV-2: MISMATCHED LENGTH, POSSIBLE TRUNCATION'
when b.format ne a.format then
'SEV-3: MISMATCHED FORMAT, POSSIBLE MISINTERPRETATION'
else
'OK'
end as RULE
from
(select name, length, type, format from sashelp.vcolumn
where libname="WORK" and memname="_TMPREF") a
full join
(select name, length, type, format from sashelp.vcolumn
where libname="WORK" and memname="_TMPIN") b
on (upcase(a.name)=upcase(b.name))
order by RULE
;
*drop view work._tmpref;
* drop view work._tmpref;
quit;
%mend check_reference_data ;
%check_reference_data
(out_adc20141 =sashelp.class(drop=age) /* name of input table to compare */
,out_adc20134=sashelp.class /* name of reference table for comparison */
,out_result=test1 /* new data set to hold result */
);
