ERROR: There were 1 unclosed %DO statements.

[Data_Detective_23219]

The intention of this code to get every permutation of copay onto one line for the 3 by variables listed within the macro.  (i.e. SubscriberCopay_Crowns, SpouseCopay_Crowns, etc... for 5 different relationships , for 20 types of services).

I've written loops on many occasions but this one in particular is giving me an error as specified in the discussion title.

proc sql;
  select distinct translate(translate(trim(ValidRelationshipDescription),'_','/'),'_',' ')
  into :Relationship1 - :Relationship5 from copay2;
quit;

proc sql;
  select distinct _NAME_ into :Service1 - :Service20 from copay3;
quit;

%macro expand;
data copay4;
  set copay3;
  by Groupnumber NetworkGroupID NetworkGroupIDDescription;
   %do i = 1 %to 5;
      %do k = 1 %to 20;
    %if first.NetworkGroupID %then %do;
     %if _NAME_ = "&&service&k.." %then %do;
     &&Relationship&i.._&&Service&k.. = &&Relationship&i..;
     %end;
   %end;
%end;
run;
%mend expand;
%expand;

dataset copay3 example line:

Groupnumber, NetworkGroupID, NetworkGroupIDDescription, _NAME_, Any, Dependants_Only, Subscriber_Spouse, Subscriber_Only, Spouse_Dependants

00000-00001                  1                         All Networks                        Crown    50                    .                           .                                .                         .

[Reeza]

You have 4 %do and 3 %end so the error is correct.

You need to add another %end or remove a %do

[ballardw]

My guess would be that this construct:

     %if _NAME_ = "&&service&k.." %then %do;

     &&Relationship&i.._&&Service&k.. = &&Relationship&i..;

should be:

     %if _NAME_ = "&&service&k.." %then

     &&Relationship&i.._&&Service&k.. = &&Relationship&i..;

[Data_Detective_23219]

Thanks ballardw.  Your answer was correct.  I had a redundant do in my condition.  Suprising I can still get completely flummoxed by such trivial errors.

[DrAbhijeetSafai]

In my case, I had closed a %do loop, so I was not able to find out the error. Later I found out with the help of a colleague (Sushil)  that it was closed with end (and not %end). So the problem was I was trying to close %do loop with end and not %end, so the error. 

Thank you.

- Dr. Abhijeet Safai

[Tom]

---

@DrAbhijeetSafai wrote:

In my case, I had closed a %do loop, so I was not able to find out the error. Later I found out with the help of a colleague (Sushil)  that it was closed with end (and not %end). So the problem was I was trying to close %do loop with end and not %end, so the error. 

Thank you.

- Dr. Abhijeet Safai

---

Not really.

The code you posted has the opposite problem.  You are using MACRO code where you need to be using actual SAS code.

For example these two statements make no sense at all.

%if first.NetworkGroupID %then %do;
%if _NAME_ = "&&service&k.." %then %do;

The string first.NetworkGroupID that you are asking the macro processor to evaluate as a boolean expression is not one.  

And no matter what the macro variables resolve to this test _NAME_ = "&&service&k.." will always be false to the macro processor since the string on the left starts with an underscore and the string on the right starts with a double quote character.

If you want to test the value of a variable like first.NetworkGroupID and _NAME_ then you need to use SAS code and not MACRO code.

[DrAbhijeetSafai]

@Tom  Thanks for your response. 

However, I have not posted any code. Maybe you wanted to say - The code posted here has opposite problem. 

Thank you.

- Dr. Abhijeet Safai