topic Re: Problems doing a loop in SAS Macro in SAS Programming

Problems doing a loop in SAS Macro

Debugger — Mon, 17 Feb 2020 14:24:16 GMT

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,

Former Post

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

Re: Problems doing a loop in SAS Macro

PaigeMiller — Mon, 17 Feb 2020 14:38:00 GMT

@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.

Re: Problems doing a loop in SAS Macro

Reeza — Mon, 17 Feb 2020 18:56:23 GMT

Wouldn't you design the macro such that any issues, such as missing are explicitly highlighted for the end user?

Before starting with macros you should have working code that works for a single iteration, can you post that version and then an example of how you need to generalize it?