topic Re: How do I use/refer a macro variable in a macro parameter? in SAS Programming

How do I use/refer a macro variable in a macro parameter?

Pili1100 — Thu, 04 Nov 2021 22:40:48 GMT

I want to create TableB, TableC, TableD, TableE from TableA using macros.

How do I use/refer a macro variable in a macro parameter?
Can I use/refer & in a parameter statement? See &vars

%LET keep_vars_1_mm =
DateKey_MdStart
,DateKey_MdEnd
,Year
,YEARQQ
,YEARMO
,Group_level_1
,Group_level_2
;
%LET keep_vars_2_mm =
DateKey_MdStart
,DateKey_MdEnd
,Year
,YEARQQ
,YEARMO
,Group_level_1

%LET keep_vars_3_qtr =
DateKey_MdStart
,DateKey_MdEnd
,Year
,YEARQQ
,Group_level_1
,Group_level_2
;
%LET keep_vars_4_qtr =
DateKey_MdStart
,DateKey_MdEnd
,Year
,YEARQQ
,Group_level_1

;
%LET Date1=DateKey_MdStart;
%LET Date2= DateKey_MdEnd;

%MACRO MyMacro(lib=, dataset=, vars=, from_lib=, from_dataset=);
PROC SQL;
CREATE TABLE &lib..&dataset AS
&vars
,(SUM(Numeric_var) AS X
FROM &from_lib..&from_dataset
WHERE &Date1 >= INTNX('qtr', today()-1, 0, 'B')
AND &Date2 <= INTNX ('qtr', today()-1, 0, 'E')
GROUP BY
&vars
ORDER BY
&vars
;
QUIT;
%MEND;
%MyMacro(lib=WORK, dataset=TableB, vars=&keep_vars_1_mm, from_lib=LibA, from_dataset=TableA);
%MyMacro(lib=WORK, dataset=TableC, vars=&keep_vars_2_mm, from_lib=LibA, from_dataset=TableA);
%MyMacro(lib=WORK, dataset=TableD, vars=&keep_vars_3_qtr, from_lib=LibA, from_dataset=TableA);
%MyMacro(lib=WORK, dataset=TableE, vars=&keep_vars_4_qtr, from_lib=LibA, from_dataset=TableA);

Re: How do I use/refer a macro variable in a macro parameter?

Reeza — Thu, 04 Nov 2021 23:02:54 GMT

You need to tell SAS to ignore the comma's in those macro parameters.

Details and examples here:
https://blogs.sas.com/content/sgf/2019/07/17/passing-comma-delimited-values-into-sas-macros-and-macro-functions/

Re: How do I use/refer a macro variable in a macro parameter?

Tom — Fri, 05 Nov 2021 03:23:04 GMT

If you want to pass a list of values in as the value of a parameter to a macro then DO NOT USE COMMAS in the list.

If you have commas then you need to use either macro quoting of the commas:

%MyMacro(lib=WORK, dataset=TableB, vars=%quote(&keep_vars_1_mm), from_lib=LibA, from_dataset=TableA);

or actual quoting around the commas (which the macro might need to remove) or else the commas will look like parameter separators in the macro call.

Just design the macro to take a space delimited list of variables:

%LET Date1=DateKey_MdStart;
%LET Date2= DateKey_MdEnd;
%LET keep_vars_1_mm = DateKey_MdStart DateKey_MdEnd Year YEARQQ YEARMO Group_level_1 Group_level_2 ;
%MyMacro(lib=WORK, dataset=TableB, vars=&keep_vars_1_mm, from_lib=LibA, from_dataset=TableA);

Let the macro figure out if the spaces need to be converted to commas or not.

For your example you could just use PROC SUMMARY to do the summation in which case you really don't want to commas.

%macro MyMacro(lib=, dataset=, vars=, from_lib=, from_dataset=);
%if not %symexist(date1) %then %put ERROR: Required macro variable not defined.;
%else %do;

proc summary data=&from_lib..&from_dataset nway ;
  where &Date1 >= INTNX('qtr', today()-1, 0, 'B')
    and &Date2 <= INTNX ('qtr', today()-1, 0, 'E')
  ;
  class &vars ;
  var Numeric_var ;
  output out=&lib..&dataset sum=X ;
run;

%end;
%mend MyMacro;

If you really truly have to use PROC SQL so that you have to insert all of those annoying commas then it is not hard to convert the spaces to commas so SQL will be happy.

%macro MyMacro(lib=, dataset=, vars=, from_lib=, from_dataset=);
%let vars=%sysfunc(translate(%sysfunc(compbl(&vars)),%str(,),%str( )));
PROC SQL;
CREATE TABLE &lib..&dataset AS &vars, ....

Re: How do I use/refer a macro variable in a macro parameter?

Pili1100 — Fri, 05 Nov 2021 08:55:06 GMT

I tried

%let vars=%sysfunc(translate(%sysfunc(compbl(&vars)),%str(,),%str( )));

and it worked like a charm! Thanks! 😄