How to compare table structure in SAS

Question

I am testing and I need to compare two dataset structures (not data tables) in SAS. I tried using "proc compare", but it compares the data. I want to compare the structure of a dataset / table (column name, data type, null constraints, etc.)

Can anyone help?

Answer 1

You can poll views in SASHELP (vtable, vcolumn, etc.) to do this. A quick way is to create a temporary table from sashelp.vcolumn for each of the two tables you want to compare, and then use the PROC SQL join to compare them. Then you will compare the structures that are represented in the data from vcolumn.

To start with this, look at SASHELP.vcolumn.

Here is a basic example of using this method to compare variables in two data sets.

* 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 generalize this to other attributes, such as data type, length, label, etc., all of which are available in vcolumn.

• Note that you may need to modify this code to accommodate the librefs that your datasets may have.

Answer 2

proc contents, proc compare, , . out2 , . . , , CRDATE ( ) LIBNAME MEMNAME, .

/* 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;

Answer 3

1- PROC CONTENTS, ( , , , ...)

2- PROC SORT

3- PROC COMPARE.

***********************************************;
proc content data=table1 out=cont1 noprint;
run;

proc content data=table2 out=cont2 noprint;
run;

proc sort data=cont1; 
  by memname name;
run;

proc sort data=cont2; 
  by memname name;
run;

proc compare listvar
  base=cont1
  compare=cont2;
  id memname name;
run;
******************* END ************;