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;
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.
> 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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: