Hi guys,
one problem is solved, the next arrives.
I am trying to automatize a comparison between one table (Table1) and its values and a lot of other tables, that should have the same respective values. There are probably missing values in the other tables and as Table1 is updated on a regular basis, this kind of macro would make my life much easier.
The comparison shall be executed in a way, so that the macro loops a comparison between Table1 and and the respective other table by joining them.
As a result I would like to have an output of a lot of different tables, that include the joins of the respective tables, so that any executor of the macro can easily spot missing values or matching values.
In post XY I already posted the creation of the macro variables via SQL,
Once again here the code.
proc sql; select count(*) into :n from work.Tabelle1; %let n=&n; Quit; proc SQL; Select distinct NAME into :name1 - from work.Tabelle1; Quit; Proc SQL; select distinct Tabellenname into :Joinfeld1 - from work.Tabelle1; Quit;
As loop I created the code:
%Macro Loop; %Do 1 %to &n; PROC SQL; CREATE TABLE Tabelle&n AS SELECT t1.Stichwort, t1.Stichworttext, t1.NM, t1.Text, t1.NAME, t1.Tabellenname, t2.&Joinfeld FROM &Tabelle FULL JOIN &tabelle t2 ON (t1.NM = t2.&Joinfeld) WHERE t1.NM NOT = '' AND t2.Joinfeld NOT = ''; QUIT; %End; %Mend Loop; %Loop;
My Idea was to have variable "names" via which you can join the tables ("Joinfeld"), have distinct names for the tables ("tabelle") and iterations ("n").
Does this seems logical or did i commit another easy error?
Before completing the loop. This Error message occurs:
ERROR: An unexpected semicolon occurred in the %DO statement.
ERROR: A dummy macro will be compiled.
Can u guys detect the probably very easy source of the mistake and realize whether my loop would work in the first place?
Thanks in advance.
Best
@Debugger wrote:
As loop I created the code:
%Macro Loop; %Do 1 %to &n; PROC SQL; CREATE TABLE Tabelle&n AS SELECT t1.Stichwort, t1.Stichworttext, t1.NM, t1.Text, t1.NAME, t1.Tabellenname, t2.&Joinfeld FROM &Tabelle FULL JOIN &tabelle t2 ON (t1.NM = t2.&Joinfeld) WHERE t1.NM NOT = '' AND t2.Joinfeld NOT = ''; QUIT; %End; %Mend Loop; %Loop;My Idea was to have variable "names" via which you can join the tables ("Joinfeld"), have distinct names for the tables ("tabelle") and iterations ("n").
should say:
%do i=1 %to &n;
but even that doesn't fix the problem as your loop variable doesn't appear inside the %DO loop, so there are many problems. Another problem is that your WHERE statement ought to say
WHERE t1.NM NOT = '' AND t2.&Joinfeld NOT = '';
and still there may be more problems.
To further your own debugging, use the command
options mprint symbolgen mlogic;
at the start of your program and run the program again. This will produce a LOG that ought to enable you to debug the problem better.
To further our own attempts to help you, please use the above options statement, and show us the entire LOG for the macro — not just the error messages, but the entire log. Please copy the log as text and paste it into the window that appears when you click on the {i} icon, this makes the log format properly when we see it here in the SAS communities. DO NOT SKIP THE CLICKING ON THE {i} ICON STEP.
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 25. 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.