Compare the structure of two tables

[MikeFox]

Hello everyone!

There's a task I'm working on that seemed quite easy but I can't manage to pull it off. I have produced a table (lets call it source) whose records have two variables: TB1 and TB2. These two columns are the full path of a sas dataset, what I want to do is go through the whole source table and compare TB1 and TB2. If the two table have a different number of variables I increment a counter by one. In the end I display the number of different tables. Here's what I tried:

OPTIONS NOQUOTELENMAX;
%macro compare_variable_counts;
  /* Initialize counter */
  %let counter = 0;

  /* Iterate over rows in table_comparison */
  proc sql noprint;
    select count(*) into :num_rows from table_comparison;
    quit;

  %do i = 1 %to &num_rows;

  	
    /* Read table names from table_comparison */
    proc sql noprint;
      select STRING, STRING1 into :first_table, :second_table
      from table_comparison
      where monotonic() = &i;
      quit;

    /* Count variables in the first table */
    proc contents data="&first_table" out=first_vars noprint;
    run;

    data _null_;
      set first_vars nobs=num_vars_first;
      call symputx('num_vars_first', num_vars_first);
      stop;
    run;

    /* Count variables in the second table */
    proc contents data="&second_table" out=second_vars noprint;
    run;

    data _null_;
      set second_vars nobs=num_vars_second;
      call symputx('num_vars_second', num_vars_second);
      stop;
    run;

    /* Compare variable counts */
    %if %eval(&num_vars_first ne &num_vars_second) %then %do;
      /* Increment counter if variable counts are different */
      %let counter = %eval(&counter + 1);
    %end;
  %end;

  /* Display the final count */
  %put The number of tables with different variable counts: &counter;
%mend compare_variable_counts;

The problem is that I can't use the full path (it's just too long), so I'm kinda stuck. Any suggestion?

[PaigeMiller]

Please tell us an example of this too long full path.

If the full path is too long, it is an operating restriction that is the limit, not SAS, and you really can't get around this, other than by decreasing the length by not putting files nested so deeply in a folder structure.

[ballardw]

You may also say yourself some headaches by using Proc Compare.

A brief example creating a data set from one you should have available so you can see a brief example of the sort of differences may be reported:

data work.class;
   set sashelp.class;
   newvar = weight/height;
   drop name;
run;

proc compare base=sashelp.class compare=work.class
   novalues listbasevars listcompvars;
run;

The NOVALUES option suppresses the default behavior of reporting differences in values by observation, which could be quite lengthy. The LISTBASEVARS and LISTCOMPVARS indicate the names of the variables that only appear in one set.

However this won't help at all with the path issues. You don't mention what operating system you use but Windows/DOS as a SUBST statement that allows creating a path substituition so that you can reference a long path:

C:\thisfolder\anotherfolder\verylongnamedpathfolder\wanttouse for example

The following is an OPERATING SYSTEM command

subst Z: C:\thisfolder\anotherfolder\verylongnamedpathfold;  

and you could use

z:\wanttouse\<filename>

to access files in that folder.

I'm sure there is something similar in UNIX but I don't know it off hand.

[Amir]

Hi @MikeFox,

If the columns TB1 & TB2 hold full paths to data sets, then are you assigning librefs to those paths using, for example, a libname statement, before using the SAS procedures to interrogate the data sets in those paths?

Kind regards,

Amir.