BookmarkSubscribeRSS Feed
Data_Detective_23219
Calcite | Level 5

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

6 REPLIES 6
Reeza
Super User

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

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

ballardw
Super User

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
Calcite | Level 5

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
Pyrite | Level 9

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

Dr. Abhijeet Safai
Associate Data Analyst
Actu-Real
Tom
Super User Tom
Super User

@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
Pyrite | Level 9

@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

Dr. Abhijeet Safai
Associate Data Analyst
Actu-Real

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 7551 views
  • 1 like
  • 5 in conversation