BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

Hello:

I created a macro codes below.  It showed several syntax errors in Log.  Please note that, the reason I put "&X1-&X5" is I might have more X coming input in the future, it could be up to 100.   That is why I use '-' to save my time to list all of the texts.  However, it seems SAS don't recognize this function.  Please advice how to fix it.  Thanks.

 

%Let X1='1400000US06001401300';

%Let X2='1400000US06037131400';

%Let X3='1400000US06037408627';

%Let X4='1400000US06037482201';

%Let X5='1400000US06111005306';

 

data want;

set have;

where geo_id in (&X1-&X5);

run;

 

708 data want;

709 set have;

710 where geo_id in (&X1-&X5);

                                         -

                                       22

                                      200

710 where geo_id in (&X1-&X5);

-

22

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ), ','.

ERROR 200-322: The symbol is not recognized and will be ignored.

710 where geo_id in (&X1-&X5) or GEO_display_label in (&Y1-&Y5);

-

200

ERROR 200-322: The symbol is not recognized and will be ignored.

ERROR: Syntax error while parsing WHERE clause.

711 run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

First don't make hundreds of macro variables. Leave data in datasets.

In PROC SQL code you could write something like this:

where geo_id in (select X from xlist)

And if you do put them into macro variables why use so many?  Why not just put the values into a single macro variable?

where geo_id in (&xlist)

If you really really need to generate &x1 to &x100 then you will need a macro.

%macro expand(base,n);
%local i;
%do i=1 %to &n; &&&base.&i %end;
%mend expand;

....
where geo_id in (%expand(x,100)) ;

View solution in original post

8 REPLIES 8
Tom
Super User Tom
Super User

What code did you want the macro processor to generate?

This is what you told it to generate.

where geo_id in ('1400000US06001401300'-'1400000US06111005306');

It would probably be easier to just use one macro variable. 

%Let Xlist=
 '1400000US06001401300'
 '1400000US06037131400'
 '1400000US06037408627'
 '1400000US06037482201'
 '1400000US06111005306'
;
.....
where geo_id in (&xlist);
ybz12003
Rhodochrosite | Level 12

As you could see, the geo_id is not continuous.

ybz12003
Rhodochrosite | Level 12

The correct one is

where geo_id in (&X1, &X2, &X3, &X4, &X5);

 

However if I have 100, do I have to write 100 Xs?

 

Tom
Super User Tom
Super User

First don't make hundreds of macro variables. Leave data in datasets.

In PROC SQL code you could write something like this:

where geo_id in (select X from xlist)

And if you do put them into macro variables why use so many?  Why not just put the values into a single macro variable?

where geo_id in (&xlist)

If you really really need to generate &x1 to &x100 then you will need a macro.

%macro expand(base,n);
%local i;
%do i=1 %to &n; &&&base.&i %end;
%mend expand;

....
where geo_id in (%expand(x,100)) ;
ybz12003
Rhodochrosite | Level 12

Hi Tom:

I am confused that why sometime is base, sometime is x; why put three & in front of base.   Please see my highlights.

 

%macro expand(base,n);
%local i;
%do i=1 %to &n; &&&base.&i %end;
%mend expand;

....
where geo_id in (%expand(x,100)) ;

Tom
Super User Tom
Super User

Do you know how SAS macro processor works?  If you want to use it then you should learn about it.

Here is a brief description of this macro definition.

 

%macro expand(base,n);
%local i;
%do i=1 %to &n; &&&base.&i %end;
%mend expand;

The first line and the last line are what makes this block of code a macro definition.  The name of the macro is EXPAND. Including the name on the %MEND statement is not required, but SAS would give me a warning that something had gotten messed up if the two names didn't match. The values BASE and N on the first line are defining the parameter names that the macro uses. 

 

 

The second line defines the macro variable I as local to macro.  I will be used as a loop counter and making local means that running the macro won't change the value of any macro variable named I that already existed when I called the macro.  Note that the macro parameters will be local macro variables.

 

The third line is the logic of what the macro is doing. It is using a %DO %TO %END loop to iterate the value of I from 1 to the value of the parameter N.

 

When the SAS macro processor sees two &'s next to each other it replaces them with one and tells itself that it needs to scan that code again to see if there might be more macro variable references to resolve. Macro variable references like '&base' and '&i' will be replaced by their values. Note that the period after '&base' let's the macro processor  know that this is the end of the name of the macro variable. 

So '&&&base.&i' will get translated to '&x1' but SAS knows it is not done yet so it will now evaluate this string for any macro variable reference.  So '&x1' will get replaced with the value of the first of many macro variables.  Note if you have not already defined a macro variable named X1 then the macro processor will complain and then just pass &x1 onto SAS and let SAS see if it makes any sense of those characters as actual SAS code.

 

Then the last line

where geo_id in (%expand(x,100)) ;

is an example of using this macro inside your block of SAS code.  When you call, or invoke, a macro your prefix the macro's name with an % and provide values for the parameters inside the () that follow.

 

In this case we are telling the macro to use X as the value for &BASE and 100 as the value for &N. 

So basically we are asking for it generate the values of macro variables X1 X2 ..... X100 to replace the text of the macro call.

 

ybz12003
Rhodochrosite | Level 12

Thank you so much for your thorough explanation, Tom!  You are awesome!

Reeza
Super User

@ybz12003 I think Tom alluded to this, but I'll expand on it. When you write a macro the code generated must be valid SAS code. The code being generated is not valid SAS code. So you need to modify it to be valid, as well as make sure it's logically valid. 

The best approach to solving these issues and debugging them is to FIRST generate working SAS code and then turn it into a macro. First by using macro variables and then a full macro if desired. 

 

The steps to writing a macro are: 

 

Step 1: Working Base SAS code

Step 2: Identify parts that need to be dynamic/looped. 

Step 3: Add the dynamic parts via macro variables

Step 4: Convert to a full macro, if required. 

Step 5: Debug using the following, if required.

options mprint symbolgen mlogic;

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
  • 8 replies
  • 3762 views
  • 4 likes
  • 3 in conversation