topic Re: mask commas in a macro variable in SAS Programming

mask commas in a macro variable

MART1 — Wed, 01 May 2024 16:42:53 GMT

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

Re: mask commas in a macro variable

PaigeMiller — Wed, 01 May 2024 17:05:19 GMT

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.

Re: mask commas in a macro variable

data_null__ — Wed, 01 May 2024 17:23:04 GMT

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

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

Re: mask commas in a macro variable

Cynthia_sas — Wed, 01 May 2024 17:48:30 GMT

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

Re: mask commas in a macro variable

MART1 — Wed, 01 May 2024 20:03:37 GMT

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

Re: mask commas in a macro variable

Tom — Wed, 01 May 2024 20:14:32 GMT

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;

Re: mask commas in a macro variable

Patrick — Thu, 02 May 2024 06:39:59 GMT

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