%goto not working as intended. Overwriting dataset or ignoring syntax

[Vivy1]

Hi,

I'm trying to write a code to skip a block of syntax if i=5 or 6 in a do loop but I find that it is not working as intended. Only the %skip label ends up running and either overwrites the datasets from the first block of code or perhaps the first block of code is getting ignored for some reason. Anyone have tips for troubleshooting? Using SAS 9.4

%macro link;
%do i=1 %to 9;
%if &i=5 or &i=6 %then %goto skip;
proc sql noprint; 
create table link_dth&i as
select  a.DTHID&i, a.year as DTH_YEAR, a.UID4S&i as DTH_UID4S&i, b.UID4S&i as PMP_UID4S&i,
b.SEQ as SEQ, b.year as PMP_YEAR
from in.cleanall_test_UID4S&i as a, pmp.cleanall_test_UID4S&i as b
where a.UID4S&i=b.UID4S&i;
quit;

%skip:
proc sql noprint; /*Link PMP with SPARCS 5,6*/
create table link_dth&i as
select a.DTHID&i, a.year as DTH_YEAR, a.UID4S&i as DTH_UID4S&i, a.gender_DTH as DTH_GENDER,
b.UID4S&i as PMP_UID4S&i, b.SEQ as SEQ,
b.year as PMP_YEAR, b.gender_PMP as PMP_GENDER
from in.cleanall_test_UID4S&i as a, pmp.cleanall_test_UID4S&i as b
where a.UID4S&i=b.UID4S&i and a.gender_DTH=b.gender_PMP;
quit;
%end;
%mend;

[ballardw]

Goto branches the program to that point. If you  do not control entrance to that branch for other values then that code executes as you found out. So you need a second %goto to branch past the other code you do not want.

%macro link;
   %do i=1 %to 9;
      %if &i=5 or &i=6 %then %goto skip;
      proc sql noprint; 
      create table link_dth&i as
      select  a.DTHID&i, a.year as DTH_YEAR, a.UID4S&i as DTH_UID4S&i, b.UID4S&i as PMP_UID4S&i,
      b.SEQ as SEQ, b.year as PMP_YEAR
      from in.cleanall_test_UID4S&i as a, pmp.cleanall_test_UID4S&i as b
      where a.UID4S&i=b.UID4S&i;
      quit;
      %goto theend;

      %skip:
      proc sql noprint; /*Link PMP with SPARCS 5,6*/
      create table link_dth&i as
      select a.DTHID&i, a.year as DTH_YEAR, a.UID4S&i as DTH_UID4S&i, a.gender_DTH as DTH_GENDER,
      b.UID4S&i as PMP_UID4S&i, b.SEQ as SEQ,
      b.year as PMP_YEAR, b.gender_PMP as PMP_GENDER
      from in.cleanall_test_UID4S&i as a, pmp.cleanall_test_UID4S&i as b
      where a.UID4S&i=b.UID4S&i and a.gender_DTH=b.gender_PMP;
      quit;

      %theend; 
   %end;
%mend;

Please post code and log entries in a text box opened on the forum using the </> icon that appears above the message box to preserve formatting. Also when the code or log is in such a box it is visually separated from the question/discussion text. If you want some code highlighting you can also post code into a code box opened with the "running man" icon.

[Kurt_Bremser · Accepted solution]

Instead of using %GOTO, wrap the two blocks in mutually exclusive %DO-%END:

%macro link;
%do i = 1 %to 9;
  %if &i ne 5 and &i ne 6
  %then %do;
    proc sql noprint; 
    create table link_dth&i as
      select  a.DTHID&i, a.year as DTH_YEAR, a.UID4S&i as DTH_UID4S&i, b.UID4S&i as PMP_UID4S&i,
       b.SEQ as SEQ, b.year as PMP_YEAR
      from in.cleanall_test_UID4S&i as a, pmp.cleanall_test_UID4S&i as b
      where a.UID4S&i=b.UID4S&i
    ;
    quit;
  %end;
  %else %do;
    proc sql noprint; /*Link PMP with SPARCS 5,6*/
    create table link_dth&i as
      select a.DTHID&i, a.year as DTH_YEAR, a.UID4S&i as DTH_UID4S&i, a.gender_DTH as DTH_GENDER,
      b.UID4S&i as PMP_UID4S&i, b.SEQ as SEQ,
      b.year as PMP_YEAR, b.gender_PMP as PMP_GENDER
      from in.cleanall_test_UID4S&i as a, pmp.cleanall_test_UID4S&i as b
      where a.UID4S&i=b.UID4S&i and a.gender_DTH=b.gender_PMP
    ;
    quit;
  %end;
%end;
%mend;