how to compare table structure in SAS

Question

I am a tester and i need to compare two data sets structure (not table data) in SAS. I tried to use 'proc compare' but it compares the data. i want to compare dataset/table structure (column name, data type, null constraints etc.)

Can any one pl help. thanks

Answer 1

You can interrogate the views in SASHELP (vtable, vcolumn etc) to do this. A quick way would be to create a temporary table from sashelp.vcolumn for each of the two tables you want to compare, then use a PROC SQL join to compare them. Then you'll be comparing the structures, which is represented in the data from vcolumn.

To get started with this, have a look at what's in SASHELP.vcolumn.

Here is a basic example of employing this method, to compare variables in 2 datasets.

* provide names of the two data sets here ;
%let ds1=TheFirstDataSet;
%let ds2=TheOtherDataSet;

* upcase the data set names ;
%let ds1=%sysfunc(upcase(&ds1));
%let ds2=%sysfunc(upcase(&ds2));

proc sql;
* retrieve info on these tables from sashelp.vcolumn;
  create table first as select * from sashelp.vcolumn where upcase(memname)="&ds1";
  create table second as select * from sashelp.vcolumn where upcase(memname)="&ds2";
* join these data sets and report on differences for var names;
  select coalescec(f.name,s.name) as varName
        ,case
          when f.name is null then "This var is in &ds2 only"
          when s.name is null then "This var is in &ds1 only"
          else 'This var is in both data sets'
          end as DiffDescription
  from 
    first as f
    full outer join 
      second as s 
      on f.name=s.name
  ;
quit;

You can generalise from this for other attributes such as data type, length, label etc., all of which are available in vcolumn.

• Note that you may need to alter this code to accommodate librefs that your data sets may have.

Answer 2

You can write the descriptor portions to data sets using proc contents then use proc compare to see how their structures differ. The out2 option will write out integrity constraints if they exist. If not the data set will be empty. Some columns like CRDATE (creation date) or LIBNAME or MEMNAME, may be expected to differ and so you may wish to exclude those from the comparison.

/* create some fake data similar to an existing one */
proc sql;
create table myclass as 
  select *, "foo" as newcol from sashelp.class
;
/* modify it */
  insert into myclass
     values ("George", "M", 17, 72, 169,"foo");
/* add an index */
  create index names on
     work.myclass(name, age);
quit;

/* write out descriptor portions to data sets */
proc contents data=myclass out=ds1 out2=ds2;run;
/* sashelp.class doesn't have an index so ds2a will not exist */
proc contents data=sashelp.class out=ds1a out2=ds2a;run;

/* compare data set structures */
proc compare data=ds1 compare=ds1a;run;