Hi @noda6003
I found some code which I think does what you want. It reports any variables not found in reference together with variables with different attributes. It does not report missing variables compared to reference, because in my case not all data sets are expected to contain all variables.
Any differences are shown as the attribute value followed by the atrtribute value from the reference data set in parentheses.
Note also that my code expects reference data set to be in the same library, but it only a minor correction to the code to change that. I had to make som test data, because I didn't have a real example with all types of differences.
Test data
libname mylib 'c:\temp\testlib';
data mylib.dictionary;
attrib id length=8 label='Identity';
attrib name length=$34 label='Person Full name';
attrib amount length=8 format=commax12.0;
attrib qt length=8 label='Quantity' ;
run;
data mylib.a;
attrib id length=8 length=5;
attrib name length=$34 label='PersonName';
attrib sales_amount length=8 format=commax12.0;
run;
data mylib.b;
attrib ID length=$8 label='Identity';
attrib name length=$40 label='Person Full name';
attrib amount length=8 format=12.0;
attrib qt length=5 label='Quantity Sold';
;
run; program to report differences:
/***************************************************************************/
/* Report variables in library not found in reference or with different */
/* attributes (varname upper/lowercase, label, type, length, format) */
/* Missing variables compared to reference are not included */
/* If attributes are different, the attribute is shown together with the */
/* atrtribute from reference in parentheses. */
/***************************************************************************/
%let mydict = dictionary;
%let mypath = c:\temp\testlib;
proc sql;
create table diff as
select
a.memname,
a.name,
a.label,
a.type,
a.length,
a.format,
b.name as refname,
b.label as reflabel,
b.type as reftype,
b.length as reflength,
b.format as refformat
from (
select memname, name, label, type, length, format
from sashelp.vcolumn
where libname = 'MYLIB' and memname ne upcase("&mydict")
) as a
left join (
select memname, name, label, type, length, format
from sashelp.vcolumn
where libname = 'MYLIB' and memname = upcase("&mydict")
) as b
on upcase(a.name) = upcase(b.name)
where
a.name ne b.name or
a.label ne b.label or
a.type ne b.type or
a.length ne b.length or
a.format ne b.format
order by memname, name;
quit;
data report; set diff;
length Dataset $32 VarName $65 VarLabel $256 VarType $13 VarLength $13 VarFormat $67;
keep Dataset VarName VarLabel VarType VarLength VarFormat;
Dataset = memname;
if refname = '' then do;
VarName = trim(name) || ' (Variable not in Ref.)';
VarLabel = '-';
VarType = '-';
VarLength = '-';
VarFormat = '-';
end;
else do;
if name ne refname then
VarName = trim(name) || ' (' || trim(refname) || ')';
else VarName = name;
if label = '' and reflabel ne '' then
VarLabel = '-none- (' || trim(reflabel) || ')';
else if label ne '' and reflabel = '' then
VarLabel = trim(label) || ' (-none-)';
else if label ne reflabel then
VarLabel = trim(label) || ' (' || trim(reflabel) || ')';
if type ne reftype then
VarType = trim(type) || ' ('||trim(reftype) || ')';
if length ne reflength then
VarLength = strip(put(length,8.)) || ' (' || strip(put(reflength,8.)) || ')';
if format = '' and refformat ne '' then
VarFormat = '-none- (' || trim(refformat) || ')';
else if format ne '' and refformat = '' then
VarFormat = trim(format) || ' (-none-)';
else if format ne refformat then
VarFormat = trim(format) || ' (' || trim(refformat) || ')';
end;
run;
proc print data=report noobs;
run;Output:
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Click image to register for webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
959 views
-
2 likes
-
-
