Proc sql within macro - where clause take values from macro variable containing list of values

[VCucu]

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

[PaigeMiller]

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.

[VCucu]

%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         

[PaigeMiller]

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.

[VCucu]

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         

[PaigeMiller]

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.

[VCucu]

sorry, this is irrelevant in my example. i need to run it like a batch based on a values from table

[VCucu]

ans there are syntax errors. i do not get any syntax error. i do not know about your proposal with str... how to implement that when running a batch

[PaigeMiller]

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.

[VCucu]

ok. without call execute is fine

[Tom · Accepted solution]

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');

[VCucu]

indeed.it works. thank you very much