Creating a loop in SAS Macro that compares a table with lots of others

[Reeza]

Yes it would hold. The tutorial does exactly that when it runs through all makes in the last step. Did you run all the code in the tutorial and did it not work that way?

[Tom]

I can help you simplify the SQL code to generate the series of macro variables from data.  

You can select more than one field and generate more than one series of macro variables in the same query.

You can capture the count of records found from the automatic macro variable SQLOBS.

I cannot see how adding the DISTINCT keyword helps as it makes no sense to me that table X would have duplicate observations for the same NAME+JOINFELD combination.

proc sql noprint;
select name
     , tabellenname
  into :name1 -
     , :joinfeld1 -
  from X
;
%let n=&sqlobs;
quit;

I cannot help with how you are going to use the macro variables as the code you posted still does not make any sense to me.  But you should now have the macro variable N and 2*N other macro variables that you can use inside your %DO loop.

%do index=1 %to &n;
...
&&NAME&INDEX 
... 
&&JOINFELD&INDEX
...
%end;

[ChrisNZ]

Why not simply use something like

proc sql;
   create table COMPARE as 

   select t1.STICHWORT_ID1 
        , t1.STICHWORT_TXT1 
        , t1.AUSPRAGUNG 
        , t1.TXT 
        , t1.NAME 
        , "Table 1" as COMP 
        , t2.JOINFIELD1    as JOINFIELD   
      from REFTABLE     t1
             left join 
           COMPTABLE1   t2 
             on t1.AUSPRAGUNG = t2.JOINFIELD1 
      where t2.JOINFIELD1 is not null

   union

   select t1.STICHWORT_ID1 
        , t1.STICHWORT_TXT1 
        , t1.AUSPRAGUNG 
        , t1.TXT 
        , t1.NAME 
        , "Table 2" as COMP 
        , t2.JOINFIELD2    as JOINFIELD   
      from REFTABLE     t1
             left join 
           COMPTABLE2   t2 
             on t1.AUSPRAGUNG = t2.JOINFIELD2 
      where t2.JOINFIELD2 is not null
;

quit;

I don't see &n used anywhere.

[Debugger]

@ChrisNZ
Achtually a good approach!!
But I think of would not fulfill my Task AS to compare the tables neatlessly. With the Union Statement IT would only Show me that Things are Not the Same...

[ChrisNZ]

> With the Union Statement IT would only Show me that Things are Not the Same...

The union keyword just appends the comparisons together.

The join keyword is where you decide what to keep.

As @Reeza said, you could also use proc compare outputs and append them.

[Debugger]

@Reeza
I already left Uni. Excited to do IT tomorrow!! Hope it works. Thanks im advance!!!

[Reeza]

Why aren't you using PROC COMPARE?

[Reeza]

And there's a much easier way to find out which ID's are in which tables, stack them all into one table and transpose it. If you need to keep multiple variables and rename that's possible with rename or KEEP via data set options.

data want;
set table1 (keep = variableList rename=(var1=ID))
     table2 
     table3 indsname=source;

dsetin = source;
Count=1;
run;

proc sort data=want;
by ID source;
run;

proc transpose data=want;
by ID;
ID Source;
var Count;
run;