Hi,
i have a macro as the one below:
%macro prog1(var=1);
proc sql;
select sum(amount) from table1 where country in (&var1.);
quit;
%mend
when i am running i am trying to pass a list of values separated by comma with var1. e.g. var1='Austria','Romania','France','Germany'
i get an error "all positional parameters must precede keyword parameters"
i tried using in above also
in ( %quote(&var1))
but i get the same error.
any way to get around this?
thank you very much
example:
DATA countries;
INPUT country $ population;
CARDS;
France 75
Germany 85
Romania 22
Austria 10
Italy 60
;
RUN;
%macro prog1(var1=);
proc sql;
select sum(population) from countries where country in (&var1.);
quit;
%mend;
%prog1(var1='France');
this works fine and results 75. however i want to run it with different clauses. i have a xls table which i import where i have all the query combinations which i must execut; i import it but then i get the error when running the macro on the table:
data NULL;
set table_to_run;
call execute(catt('%prog1(var1=',var1,');'));
run;
this is table_to_run
Why include the commas? You don't need them, the IN operator is happy to accept space delimited list. And they just make passing the value via a macro call complicated.
%prog1(var= 'France' 'Italy');
when i am running i am trying to pass a list of values separated by comma with var1. e.g. var1='Austria','Romania','France','Germany'
but you don't show us that code, and you don't show us the LOG from that code, either of which would be very helpful.
Nevertheless, I think what you want is:
%prog1(var1=%str('Austria','France','Romania','Germany'));
If that's not what you want, then please explain further and show us the code or log.
%str did not help, same error
program:
DATA countries;
INPUT country $ population;
CARDS;
France 75
Germany 85
Romania 22
Austria 10
Italy 60
;
RUN;
%macro prog1(var1=);
proc sql;
select sum(population) from countries where country in (&var1.);
quit;
%mend;
data NULL;
set sasuser.ir_input_sheet12;
call execute(catt('%prog1(var1=',var1,');'));
run;
table sasuser.ir_input_sheet12 looks like this
log is the following:
1 Das SAS System 13:16 Saturday, July 17, 2021
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Programm';
4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HtmlBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 DATA countries;
29 INPUT country $ population;
30 CARDS;
NOTE: The data set WORK.COUNTRIES has 5 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.07 seconds
cpu time 0.03 seconds
36 ;
37 RUN;
38
39 %macro prog1(var1=);
40 proc sql;
41 select sum(population) from countries where country in (&var1.);
42 quit;
43 %mend;
44
45 data NULL;
46 set sasuser.ir_input_sheet12;
47 call execute(catt('%prog1(var1=',var1,');'));
48 run;
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
NOTE: The SAS System stopped processing this step because of errors.
2 Das SAS System 13:16 Saturday, July 17, 2021
NOTE: There were 3 observations read from the data set SASUSER.IR_INPUT_SHEET12.
WARNING: The data set WORK.NULL may be incomplete. When this step was stopped there were 3 observations and 6 variables.
WARNING: Datei WORK.NULL wurde nicht ersetzt, da da dieser Schritt angehalten wurde.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds
NOTE: CALL EXECUTE generated line.
1 +;
2 +;
3 +;
49
50 %LET _CLIENTTASKLABEL=;
51 %LET _CLIENTPROCESSFLOWNAME=;
52 %LET _CLIENTPROJECTPATH=;
53 %LET _CLIENTPROJECTPATHHOST=;
54 %LET _CLIENTPROJECTNAME=;
55 %LET _SASPROGRAMFILE=;
56 %LET _SASPROGRAMFILEHOST=;
57
58 ;*';*";*/;quit;run;
59 ODS _ALL_ CLOSE;
60
61
62 QUIT; RUN;
63
In this most recent batch of code, I do not see the %STR function being used.
In addition, you have now gone to CALL EXECUTE instead of just calling the macro, so please stick with one method and ask questions about one method, do not switch back and forth.
code with str; i normally use call execute
DATA countries;
INPUT country $ population;
CARDS;
France 75
Germany 85
Romania 22
Austria 10
Italy 60
;
RUN;
%macro prog1(var1=);
proc sql;
select sum(population) from countries where country in (&var1.);
quit;
%mend;
data NULL;
set sasuser.ir_input_sheet12;
call execute(catt('%prog1(var1=',%str(var1),');'));
run;
log
1 Das SAS System 13:16 Saturday, July 17, 2021
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Programm';
4 %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5 %LET _CLIENTPROJECTPATH='';
6 %LET _CLIENTPROJECTPATHHOST='';
7 %LET _CLIENTPROJECTNAME='';
8 %LET _SASPROGRAMFILE='';
9 %LET _SASPROGRAMFILEHOST='';
10
11 ODS _ALL_ CLOSE;
12 OPTIONS DEV=SVG;
13 GOPTIONS XPIXELS=0 YPIXELS=0;
14 %macro HTML5AccessibleGraphSupported;
15 %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16 %mend;
17 FILENAME EGHTML TEMP;
18 ODS HTML5(ID=EGHTML) FILE=EGHTML
19 OPTIONS(BITMAP_MODE='INLINE')
20 %HTML5AccessibleGraphSupported
21 ENCODING='utf-8'
22 STYLE=HtmlBlue
23 NOGTITLE
24 NOGFOOTNOTE
25 GPATH=&sasworklocation
26 ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28
29 DATA countries;
30 INPUT country $ population;
31 CARDS;
NOTE: The data set WORK.COUNTRIES has 5 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
37 ;
38 RUN;
39
40 %macro prog1(var1=);
41 proc sql;
42 select sum(population) from countries where country in (&var1.);
43 quit;
44 %mend;
45
46 data NULL;
47 set sasuser.ir_input_sheet12;
48 call execute(catt('%prog1(var1=',%str(var1),');'));
49 run;
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
ERROR: All positional parameters must precede keyword parameters.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 3 observations read from the data set SASUSER.IR_INPUT_SHEET12.
2 Das SAS System 13:16 Saturday, July 17, 2021
WARNING: The data set WORK.NULL may be incomplete. When this step was stopped there were 3 observations and 6 variables.
WARNING: Datei WORK.NULL wurde nicht ersetzt, da da dieser Schritt angehalten wurde.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
NOTE: CALL EXECUTE generated line.
1 +;
2 +;
3 +;
50
51 %LET _CLIENTTASKLABEL=;
52 %LET _CLIENTPROCESSFLOWNAME=;
53 %LET _CLIENTPROJECTPATH=;
54 %LET _CLIENTPROJECTPATHHOST=;
55 %LET _CLIENTPROJECTNAME=;
56 %LET _SASPROGRAMFILE=;
57 %LET _SASPROGRAMFILEHOST=;
58
59 ;*';*";*/;quit;run;
60 ODS _ALL_ CLOSE;
61
62
63 QUIT; RUN;
64
I said in my very first reply to use
%prog1(var1=%str('Austria','France','Romania','Germany'));
Please show us code where you have done that, and the associated log.
You CALL EXECUTE has syntax errors, and it also is not likely to work the way you want it to.
You started with %prog1(var1='France') I'm afraid you have wandered around, and now have confused me.
You can run this by extracting values from a table and without CALL EXECUTE, if you want (but it sounds like you don't want to).
If you want to do this without CALL EXECUTE, I can show you how. Since I rarely use CALL EXECUTE to call macros, I will let someone else help you.
Why include the commas? You don't need them, the IN operator is happy to accept space delimited list. And they just make passing the value via a macro call complicated.
%prog1(var= 'France' 'Italy');
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.