mask commas in a macro variable

[MART1]

Hello

I need to pass commas in a macro function and I get the below error.

ERROR: All positional parameters must precede keyword parameters.

I understand to mask the commas %BQUOTE should be the best solution, but I cannot see how to apply this to my code  - below.

/*real file will come from an Excel import*/
data INFILE;
	input if_statement $ 1-36 Region $ 37-60;
	datalines;
1 if Make = 'Audi' then TEST1 = 'OK' 'Europe', 'Asia', 'USA'
2 if Make = 'BMW' then TEST2 = 'OK'  'Asia', 'USA'
3 if Make = 'Ford' then TEST3 = 'OK' 'USA', 'Asia'
;
run;

%macro makereport(Var=, Check=);

    /*run test*/
	data OUTPUT (where=(Origin IN (&Check)));
		set SASHELP.CARS;
		&Var;
	run;

%put &Var;
%put &Check;

/*additional steps to retain/store results will go here*/

%mend makereport;

data _null_;
	set INFILE;

	*builds string to execute macro;
str = catt('%makereport(Var=',if_statement,',Check=',Region,');');

	*execute macro;
	call execute(str);
run;

I have looked at many examples on the web, but none I can see where it should be applied to a code like mine (at least as I understand it):

I tried adding to  the call execute, the string that builds the macro and within the macro (though I believe it's incorrect anyway) but none of this works.

thanks

[PaigeMiller]

I strongly advise you to consider a simpler approach, which does not involve all three of: SAS code stored in a data set, macros and call execute.

In the end, I doubt this will work because it does not resolve to working SAS data step code. But I don't really know what actual SAS data step code you want here, you have not told us. So, without macros and without macro variables, show us what SAS code you want here for row 1 of the data set (or any single row), show us working SAS code that does what you want. Fill in the blanks for one case, without macros and without macro variables. Without working code here, you will never get macros and call execute to work.

	data OUTPUT (where=(Origin IN (______________)));
		set SASHELP.CARS;
                _________________________________________
	run;

In my past experience, people just sidestep or ignore the question, and never provide working code here without macros and without macro variables. Please don't sidestep or ignore the question.

[Cynthia_sas]

Hi, in addition to @PaigeMiller very good suggestion, it also helps to understand the basics of using WHERE with IN. For example, a simple test or reading the documentation

would show that commas are not required with the IN operator. Your list for the IN operator can be separated by blanks. In addition, putting the WHERE on the DATA statement forces ALL of the observations to be read (causing more I/O) especially if you're only choosing a subset of the entire input data. Putting the WHERE on the SET statement with the INPUT data is usually more efficient, which is why you start with working code before writing any macro code.

  Hope this helps.

Cynthia

[MART1]

Thank you @Cynthia_sas  @data_null__  @PaigeMiller  very helpful - I have amended the code to remove commas and place the where in the SET - now it work fine

/*real file will come from an Excel import*/
data INFILE;
	input if_statement $ 1-36 Region $ 37-60;
	datalines;
if Make = 'BMW'  then TEST2 = 'OK'  'Asia'  'USA'
if Make = 'Ford' then TEST3 = 'OK'  'USA'  'Asia'
if Make = 'Audi' then TEST1 = 'OK'  'Europe'  'Asia'  'USA'
;
run;

%macro makereport(Var=, Check=);

    /*run test*/
	data OUTPUT ;
		set SASHELP.CARS (where=(Origin IN (&Check)));
		&Var;
	run;

%put var=&Var;
%put check=&Check;

/*additional steps to retain/store results will go here*/

%mend makereport;

data _null_;
	set INFILE;

	*builds string to execute macro;
str = catt('%makereport(Var=',if_statement,';,Check=',Region,');');

	*execute macro;
	call execute(str);
run;

Hi @PaigeMiller  re what I need, the above will do - after I add more tables in the macro.

. In brief, I have a dataset where different users need to run different tests against (i.e. how many records in Column1 are greater than records in Column2, only for records with some values in Column3).

Users

-enter those tests in Excel (i.e. COL1>COL2 where COL3 IN (blue  white black) )

- import the file where SAS converts into the str

- the macro will add a column for each test - putting them together in a table using PROC APPEND or similar

-additional tables within the macro will summarise the result in a summary table

I thought the macro approach would be the best, but happy to consider anything else ?

thanks

[Tom]

 Why over complicate things? There is no need to place the where condition "ON" either the input or the output dataset. 

Just use a normal WHERE statement instead of the WHERE= dataset option. 

%macro makereport(Var=, Check=);
data OUTPUT ;
  set SASHELP.CARS;
  where Origin IN (&Check);
  &Var;
run;
%mend makereport;

[Patrick]

...and below how you could call the macro in case Region contains a comma separated list of values.

/*real file will come from an Excel import*/
data driver;
	input if_statement $ 1-36 Region $ 37-60;
	datalines;
if Make = 'BMW'  then TEST2 = 'OK'  'Asia','USA'
if Make = 'Ford' then TEST3 = 'OK'  'USA'  'Asia'
if Make = 'Audi' then TEST1 = 'OK'  'Europe'  'Asia'  'USA'
;
run;

%macro makereport(Var=, Check=);
data OUTPUT ;
  set SASHELP.CARS;
  where Origin IN (&Check);
  &Var;
run;
%mend makereport;

data _null_;
	set driver;
	*builds string to execute macro;
  str = catt('%makereport(Var=',if_statement,',Check=%nrbquote(',Region,'));');
	*execute macro;
	call execute(str);
run;

[data_null__]

The IN operator will accept blank delimited lists.  No comma needed

if region in('Europe' 'Asia' 'USA')