Help to define macro (Please)

[Anto180788]

@Tom 

Thank you so much for all your advices

%Controllo_Mesi
(Colonna_data=VARNAME
,DATA_RIF=2021
,Tab_controllo=LIBNAME.TABLENAME
)

The logic is set in this way because the macro code should be use on many datasets which have different date variable name.

DATA_RIF is composed not only the year but also the month. For example, JAN2021

put(&colonna_data,monyy7.) = "&data_rif"

So, in dataset the date varialbe name is daymonthyear (example, 25JAN2021) which is a numeric type, I need to substring only month and year in order to set the macro variable only with MONTH and YEAR.

&DATA_RIF as Data_Riferimento 

It should be good to use numeric variable.

sum(calculated Presenza_Mese_Controllo='si') as Numero_osservazioni

I changed as you said and this works better (less time consuming).

scan("&Tab_controllo",-1,'.') as Nome_Tabella

I will change also this.

Thank you so much TOM

[Quentin]

Is there anything wrong with the macro that is working?

In the macro that is not working, you changed scan to %scan, and substr to %substr.

But looking at your working macro, you are using scan and substr correctly.  

scan and substr work on the values of dataset variables.

%scan and %substr work on text, not the values of variables.

Even though your code is inside a macro definition, it's appropriate to use scan and substr when you want to use the value of a data step variable as an input to the function.

That said, Tom makes excellent points about refactoring the macro.

[Anto180788]

@Quentin 

Thank you very much Quentin

"%scan and %substr work on text, not the values of variables."
Probably this is the problem, because &colonna_data is a numeric values. So it doesn't work

[Anto180788]

Hi everyone,
many thanks for all your help to resolve my problem.

I would ask one more thing regarding the macro code which i have showed you.
Do you think is it possibile to call macro for more variable simultaneously?
For example:

%Controllo_Mesi(Colonna_data=VARNAME,DATA_RIF=2021,Tab_controllo=LIBNAME.TABLENAME)

This is normal mode and it is works.

%Controllo_Mesi(Colonna_data=VARNAME1, VARNAME2,DATA_RIF=MONTHYEAR,Tab_controllo=LIBNAME.TABLENAME1, LIBNAME.TABLENAME2)

If it isn't possible, I m thinking is better to add each new row in a existing table. So:

1) Run the macro: every time i get one row

2) Insert the new row in a existing table in order to store it.

Thank you very much

[Tom]

You cannot just stick another comma into the middle of your macro call.  That will result in more parameter values being pass in than the macro supports.  Plus then it looks like you are trying revert to passing the parameter values by position instead of by name. If you want to pass parameter values by position and by name in the same call the one's passed by position have to come first.

Use a SPACE or some other character to separate the multiple variable names.

Colonna_data=VARNAME1 VARNAME2

Then you have to add some logic in your macro for what to do when there are two variable names passed into the macro.

The normal ways to perform the same action on multiple variables is define an array and then loop over an index into the array. 

array column VARNAME1 VARNAME2;
do index=1 to dim(column);
   if column[index] ....
   ...
end;

So using a space as the delimiter is the best way to pass the list of variable names.

array column &colonna_data;

[Anto180788]

@Tom 

Thank you very much

"You cannot just stick another comma into the middle of your macro call. That will result in more parameter values being pass in than the macro supports. Plus then it looks like you are trying revert to passing the parameter values by position instead of by name. If you want to pass parameter values by position and by name in the same call the one's passed by position have to come first."

If I have to trasform my macro by parameter name, Have I need to specify also a defaut value, haven't I?

array column VARNAME1 VARNAME2;
do index=1 to dim(column);
   if column[index] ....
   ...
end;

Should I add an array for every parameter? (i.e., DATA_RIF and Tab_controllo)
Can you do an example ?

Thank you so much TOM

[Tom]

To create a macro you need to first understand what SAS code you want to create.  Then you can make decisions about what parameters you need and how those parameters will be used to generate the code you want. Whether or not you need an ARRAY statement depends more on what SAS code you are trying to get the macro to create than how/what you are passing in as the parameters to the macro.  For your macro you would need to understand what it means to pass in multiple values for the other parameters in addition to variable names.  Would that mean you want to match the first variable with the first value of the other parameter(s)?  Or combine every variable name with every value oft he other parameter?  Once you decide that you need to figure out what code you need the macro to create in those cases.

You seem confused about the deference between the macro definition:

%macro mymaco(position1,position2,name1=,name2=default); .. %mend;

And the macro call:

%mymacro;

When you include the = after the parameter name in the definition it means that the user can ONLY pass the values for that parameter by name.  You can optionally include a default value in the macro definition.  That is the value the parameter will get when it is not included in the call.  If there is no default and the parameter is not given a value in the call then the parameter will be empty (have a length of zero).

But when you CALL the macro you can pass the values by name for ANY of the parameters including those that were defined to allow the passing of values by position.  But only the parameters that have been defined as allowing for the passing of the values by position can be called without their name.

So all of these calls to MYMACRO are the same:

%mymacro(1,2,name1=xx,name2=yy)
%mymacro(position1=1,position2=2,name1=xx,name2=yy)
%mymacro(1,2,name2=yy,name1=xx)
%mymacro(position1=1,name1=xx,name2=yy,position2=2);

[Anto180788]

@Tom 

Thank you very much for your explanation.

"To create a macro you need to first understand what SAS code you want to create. Then you can make decisions about what parameters you need and how those parameters will be used to generate the code you want. Whether or not you need an ARRAY statement depends more on what SAS code you are trying to get the macro to create than how/what you are passing in as the parameters to the macro."

As I showed you, my objective is to create a report which say if there is the month and year of reference and how observation there are.

The code works on 3 parameters:  1) Date Variable ; 2) MonthYear of Control 3) dataset on which does the control

Now the macro code works setting only one value for each paramter. The result is create a table with one row with evidences.

My idea is to do this setting more values for each paramter (For example):

Variable Date: Var Date1 Var Date2 ecc

Reference Date: MonthYear1 MonthYear2

Tab of Control: Dataset1 Dataset2

The expected result is composed by two rows. Do you think I need to do an array for this?

%macro mymaco(position1,position2,name1=,name2=default); .. %mend;

%mymacro(1,2,name1=xx,name2=yy)
%mymacro(position1=1,position2=2,name1=xx,name2=yy)
%mymacro(1,2,name2=yy,name1=xx)
%mymacro(position1=1,name1=xx,name2=yy,position2=2);

Now I have understood what are you mean. Thank  you very much

Another idea is to use the actual macro code and try to add more rows in the same table (becasue the outcome table outputs only one row each run).

Thank you again

[Anto180788]

Good evening,

I have defined a macro with parameters position. I m trying to convert DATA_RIF and &sysdate9 from characeter values to numeric values in output.

rsubmit;
options mprint symbolgen mlogic noquotelenmax;
%macro Controllo_Mesi(Colonna_data,DATA_RIF,Tab_controllo);
proc sql;
insert into Tabella_sintesi 
select distinct scan("&Tab_controllo",-1) as Nome_Tabella,
          "&DATA_RIF" as Data_Riferimento ,
            "&sysdate9" as Data_Controllo ,
case when (substr(put(&Colonna_data,date9.),3,7))="&DATA_RIF" then 'si' else 'no' end as Presenza_Mese_Controllo format=$4.,
case when calculated Presenza_Mese_Controllo='si' then 
(select count(*) 
      from  &Tab_controllo 
      where (substr(put(&Colonna_data,date9.),3,7))="&DATA_RIF" )
else 0 end as Numero_osservazioni
from &Tab_controllo
group by Data_Riferimento
having calculated Numero_osservazioni;
quit;
%mend Controllo_Mesi;
endrsubmit;

Thank you very much

[Tom]

If you are writing the macro you get to decide how to use the value passed and what to tell the users of the macro how to pass the value.

If you want the DATE_RIF parameter to have a DATE value then why not pass a date value instead of plain text?

For example by having them pass in a date literal.

%macro mymacro(date_rif);
select &date_rif as datevar format=date9.
....
%mend mymacro;
%mymacro(date_rif="01JAN2021"d);

If you want to let the users pass in string in the style that the DATE informat can read then you can use that string to generate a date literal in your code.

%macro mymacro(date_rif);
select "&date_rif"d as datevar format=date9.
....
%mend mymacro;
%mymacro(date_rif=01JAN2021);
%mymacro(date_rif=1-jan-2021);

[Anto180788]

Good morning Tom,
thank you for your advices.
In my macro, the users should just put the month and year that thy want use (i.e JAN2021), because the column date on which it is applied has values as 01JAN2021. So, in order to do a easier control, I set macro only putting the month and year.
But, I would a date as numeric format and no character format if it is possibile.

Thank you very much

[Tom]

If you have macro variable, call it X, with a value like JAN2021 and you want to use it to generate a date you could use it like this:

"01&x"d

Which will resolve to

"01JAN2021"d

Which SAS will see is a date.

[Tom]

If you want to pass in a list of values as a parameter to a macro then just use some delimiter in the string you are passing.  Best is to use a space, since that is normal SAS style and will not cause issues for the macro processor.  

%macro mymacro(varlist=);
proc print data=sashelp.class;
  var &varlist;
run;
%mend ;
%mymacro(varlist=name age sex);

Worst is using a comma as then you have to add macro quoting to prevent the macro processor from seeing the commas as indicating new parameters.  If you cannot use space (because values can contain spaces) then use some other delimiter, like a | or ^ character.

%macro mymacro(names=);
%local i name;
%do i=1 %to %sysfunc(countw(&names,|));
  %let name=%scan(&names,&i,|);
  %put Name &i is &name ;
%end;
%mend;
%mymacro(names=John Smith|Jane Doe);