topic Re: Help to define macro (Please) in SAS Programming

Help to define macro (Please)

Anto180788 — Thu, 23 Sep 2021 10:14:18 GMT

Good morning everyone, I m trying to create this macro.However, everytime I try to adjust the code it says 'case when error syntax' . Why???

Thanks

options mprint symbolgen noquotelenmax;

%macro Controllo_Mesi(Colonna_data,DATA_RIF,Tab_controllo);

proc sql inobs=50;

create table Tabella_sintesi as

select distinct %scan(&Tab_controllo,2, '.') as Nome_Tabella,

&DATA_RIF as Data_Riferimento,

&sysdate9 as Data_Controllo,

case when %sysfunc(substr(&Colonna_data,3))= &DATA_RIF then si else no end as Presenza_Mese_Controllo format=$4.,

case when %sysfunc(substr(&Colonna_data,3))= &DATA_RIF and calculated Presenza_Mese_Controllo= si

then count(*) else 0 end as Numero_osservazioni

from &Tab_controllo

group by Data_Riferimento

having calculated Numero_osservazioni;

quit;

%mend Controllo_Mesi;

endrsubmit;

rsubmit;

%Controllo_Mesi(DT_OPE,JAN2021,Lgd17db.MOV_2020CONT)

endrsubmit;

LOG output:

NOTE: Remote submit to MYHOST commencing.

718 %Controllo_Mesi(DT_OPE,JAN2021,Lgd17db.MOV_2020CONT)

MPRINT(CONTROLLO_MESI): proc sql inobs=50;

SYMBOLGEN: Macro variable TAB_CONTROLLO resolves to Lgd17db.MOV_2020CONT

SYMBOLGEN: Macro variable DATA_RIF resolves to JAN2021

SYMBOLGEN: Macro variable SYSDATE9 resolves to 23SEP2021

NOTE: Line generated by the macro variable "SYSDATE9".

718 23SEP2021

-------

SYMBOLGEN: Macro variable COLONNA_DATA resolves to DT_OPE

NOTE 137-205: Line generated by the invoked macro "CONTROLLO_MESI".

718 proc sql inobs=50; create table Tabella_sintesi as select distinct %scan(&Tab_controllo,2,

718! '.') as Nome_Tabella, &DATA_RIF as Data_Riferimento, &sysdate9 as

718! Data_Controllo, case when %sysfunc(substr(&Colonna_data,3))= &DATA_RIF then si

----

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=,

<>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE,

LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS,

CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS,

ORDER, OUTER, RIGHT, UNION, WHERE.

SYMBOLGEN: Macro variable DATA_RIF resolves to JAN2021

NOTE: Line generated by the invoked macro "CONTROLLO_MESI".

718 proc sql inobs=50; create table Tabella_sintesi as select distinct %scan(&Tab_controllo,2,

718! '.') as Nome_Tabella, &DATA_RIF as Data_Riferimento, &sysdate9 as

718! Data_Controllo, case when %sysfunc(substr(&Colonna_data,3))= &DATA_RIF then si

----

ERROR 76-322: Syntax error, statement will be ignored.

SYMBOLGEN: Macro variable COLONNA_DATA resolves to DT_OPE

SYMBOLGEN: Macro variable DATA_RIF resolves to JAN2021

SYMBOLGEN: Macro variable TAB_CONTROLLO resolves to Lgd17db.MOV_2020CONT

MPRINT(CONTROLLO_MESI): create table Tabella_sintesi as select distinct MOV_2020CONT as

Nome_Tabella, JAN2021 as Data_Riferimento, 23SEP2021 as Data_Controllo , case when _OPE= JAN2021

then si else no end as Presenza_Mese_Controllo format=$4., case when _OPE= JAN2021 and calculated

Presenza_Mese_Controllo= si then count(*) else 0 end as Numero_osservazioni from

Lgd17db.MOV_2020CONT group by Data_Riferimento having calculated Numero_osservazioni;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Re: Help to define macro (Please)

PaigeMiller — Thu, 23 Sep 2021 11:29:17 GMT

Run this command and then run the macro again

options mprint symbolgen mlogic;

Then, show us the log. Please preserve the formatting of the log by copying the log as text, then clicking on the </> icon here in the SAS Communities and pasting the log into the Window that appears. DO NOT SKIP THIS STEP.

Re: Help to define macro (Please)

Quentin — Thu, 23 Sep 2021 11:48:34 GMT

Hi,

I see a few potential problems. I would suggest trying to write the SQL step without any macro code, and get it working, then go back to generating that code via macro. It's easier to debug SQL code on its own first, and then debug macro code after you know the SQL code works.

Without knowing your data, it's hard to know what will work for the CASE statements, but I would try something like:

case when OPE= "JAN2021"  then "si" else "no" end as Presenza_Mese_Controllo format=$4.,

case when OPE= "JAN2021"  and calculated Presenza_Mese_Controllo= "si" then count(*) else 0 end as Numero_osservazioni

Actually, the first error looks like it's from &sysdate resolving to a string that is not in quotes, you could change it to:

"&sysdate9" as Data_Controllo,  /*character variable*/

or:

"&sysdate9"d as Data_Controllo,   /*numeric date variable*/

Re: Help to define macro (Please)

Anto180788 — Thu, 23 Sep 2021 12:07:08 GMT

NOTE: Remote submit to MYHOST commencing.
905  %Controllo_Mesi(DT_OPE,JAN2021,Lgd17db.MOV_2020CONT)
MLOGIC(CONTROLLO_MESI):  Beginning execution.
MLOGIC(CONTROLLO_MESI):  Parameter COLONNA_DATA has value DT_OPE
MLOGIC(CONTROLLO_MESI):  Parameter DATA_RIF has value JAN2021
MLOGIC(CONTROLLO_MESI):  Parameter TAB_CONTROLLO has value Lgd17db.MOV_2020CONT


MPRINT(CONTROLLO_MESI):   proc sql inobs=50;
SYMBOLGEN:  Macro variable TAB_CONTROLLO resolves to Lgd17db.MOV_2020CONT
SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 23SEP2021
NOTE: Line generated by the macro variable "SYSDATE9".
905           23SEP2021
                -------
                22
SYMBOLGEN:  Macro variable COLONNA_DATA resolves to DT_OPE
NOTE 137-205: Line generated by the invoked macro "CONTROLLO_MESI".
905   proc sql inobs=50; create table Tabella_sintesi as select distinct %scan(&Tab_controllo,2,
905! '.') as Nome_Tabella,         &DATA_RIF as Data_Riferimento,         &sysdate9 as
905! Data_Controllo, case when %substr(&Colonna_data,3)= &DATA_RIF then si else no end
                          ----
                          22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=,
              <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE,
              LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, (, ',', '.', ANSIMISS, AS,
              CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS,
              ORDER, OUTER, RIGHT, UNION, WHERE.

SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
NOTE: Line generated by the invoked macro "CONTROLLO_MESI".
905   proc sql inobs=50; create table Tabella_sintesi as select distinct %scan(&Tab_controllo,2,
905! '.') as Nome_Tabella,         &DATA_RIF as Data_Riferimento,         &sysdate9 as
905! Data_Controllo, case when %substr(&Colonna_data,3)= &DATA_RIF then si else no end
                          ----
                          76
ERROR 76-322: Syntax error, statement will be ignored.

SYMBOLGEN:  Macro variable COLONNA_DATA resolves to DT_OPE
SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
SYMBOLGEN:  Macro variable TAB_CONTROLLO resolves to Lgd17db.MOV_2020CONT
MPRINT(CONTROLLO_MESI):   create table Tabella_sintesi as select distinct MOV_2020CONT as
Nome_Tabella, JAN2021 as Data_Riferimento, 23SEP2021 as Data_Controllo , case when _OPE= JAN2021
then si else no end as Presenza_Mese_Controllo format=$4., case when _OPE= JAN2021 and calculated
Presenza_Mese_Controllo= si then count(*) else 0 end as Numero_osservazioni from
Lgd17db.MOV_2020CONT group by Data_Riferimento having calculated Numero_osservazioni;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(CONTROLLO_MESI):   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

MLOGIC(CONTROLLO_MESI):  Ending execution.
906  endrsubmit;

Re: Help to define macro (Please)

PaigeMiller — Thu, 23 Sep 2021 12:13:33 GMT

When you use a macro variable, and then run the code, the macro variable is replaced with its value, and the result must be legal valid working SAS code.

So this part of your code

&sysdate9 as Data_Controllo,

becomes

22SEP2021 as Data_Controllo,

and this is not legal valid working SAS code. What do you need to do to fix this so that it is legal valid working SAS code?

Re: Help to define macro (Please)

Anto180788 — Thu, 23 Sep 2021 12:49:12 GMT

@QuentinThanks to reply me

You are right. I will give more informations for a better understanding.

I have to develop a macro to do a report which show if there is the reference month that are you searching in a dataset and how many observations there are.

First of all , i tried to develop the code with %let statements. I have 3 %let statements:

%let = DT_OPE ( the date variable of dataset of the test. In the dataset is a numeric variable )

%let= JAN2021 ( is the reference month which I have to control if it is present in dataset through DT_OPE

%let= lgd17db.MOV_2020CONT ( it is dataset of the test)

The report should have Name of table (name after the dot), Reference month, date of control ( done it with &sysdate9), flag of presence of reference month, number of observation of reference month.

I the cade I need to convert DT_OPE in character because i need to extract the last 7 words.

I tried to use %substr without put ,because the calling macro is already a string. So i shouldn t need the conversation. However, using %substr it results error.

Now I have tried to run the macro code without some macro functons and it works.

I don't know why the macro code gave me error using macro functons &scan, %substr.

Thank you very much

Re: Help to define macro (Please)

Quentin — Thu, 23 Sep 2021 12:58:09 GMT

Please show the macro code (without some macro functions) that works, and the macro code (with macro functions) that does not work.

Please try to make an example with only a small difference between the code that works, and the code that does not work. For example, a simpler example where the only difference is one CASE statement which does not work as you hoped:

case when %sysfunc(substr(&Colonna_data,3))= &DATA_RIF  then si else no end as Presenza_Mese_Controllo format=$4.,

But does work when you remove the macro code and replace it with SQL code.

Re: Help to define macro (Please)

Anto180788 — Thu, 23 Sep 2021 13:02:20 GMT

@PaigeMillerThank you for your observations.

I looked that i missed to write quoting marks.

Re: Help to define macro (Please)

Anto180788 — Thu, 23 Sep 2021 13:18:32 GMT

@Quentin

this is the code that works (without no macro fucntions):

NOTE: Remote submit to MYHOST complete.
243  rsubmit;
NOTE: Remote submit to MYHOST commencing.
1079  %Controllo_Mesi(DT_RIF,JAN2021,Lgd17db.SALDO_MENS)
MLOGIC(CONTROLLO_MESI):  Beginning execution.
MLOGIC(CONTROLLO_MESI):  Parameter COLONNA_DATA has value DT_RIF
MLOGIC(CONTROLLO_MESI):  Parameter DATA_RIF has value JAN2021
MLOGIC(CONTROLLO_MESI):  Parameter TAB_CONTROLLO has value Lgd17db.SALDO_MENS


MPRINT(CONTROLLO_MESI):   proc sql inobs=50;
SYMBOLGEN:  Macro variable TAB_CONTROLLO resolves to Lgd17db.SALDO_MENS
SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
SYMBOLGEN:  Macro variable SYSDATE9 resolves to 23SEP2021
SYMBOLGEN:  Macro variable COLONNA_DATA resolves to DT_RIF
SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
SYMBOLGEN:  Macro variable COLONNA_DATA resolves to DT_RIF
SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
SYMBOLGEN:  Macro variable TAB_CONTROLLO resolves to Lgd17db.SALDO_MENS
MPRINT(CONTROLLO_MESI):   create table Tabella_sintesi as select distinct
scan("Lgd17db.SALDO_MENS",2, '.') as Nome_Tabella, "JAN2021" as Data_Riferimento, "23SEP2021" as
Data_Controllo, case when substr(put(DT_RIF,date9.),3,7)= "JAN2021" then "si" else "no" end as
Presenza_Mese_Controllo format=$4., case when substr(put(DT_RIF,date9.),3,7)= "JAN2021" and
calculated Presenza_Mese_Controllo= "si" then count(*) else 0 end as Numero_osservazioni from
Lgd17db.SALDO_MENS group by Data_Riferimento having calculated Numero_osservazioni;
NOTE: At least one nonessential grouping column reference has been removed from a GROUP BY's
      reference list.
NOTE: The query requires remerging summary statistics back with the original data.
WARNING: Only 50 records were read from LGD17DB.SALDO_MENS due to INOBS= option.
NOTE: Table WORK.TABELLA_SINTESI created, with 0 rows and 5 columns.

MPRINT(CONTROLLO_MESI):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.27 seconds
      cpu time            0.02 seconds

This is the macro code with macro functions (not work):

NOTE: Remote submit to MYHOST commencing.
1198  %Controllo_Mesi(DT_OPE,JAN2021,Lgd17db.MOV_2020CONT)
MLOGIC(CONTROLLO_MESI):  Beginning execution.
MLOGIC(CONTROLLO_MESI):  Parameter COLONNA_DATA has value DT_OPE
MLOGIC(CONTROLLO_MESI):  Parameter DATA_RIF has value JAN2021
MLOGIC(CONTROLLO_MESI):  Parameter TAB_CONTROLLO has value Lgd17db.MOV_2020CONT
SYMBOLGEN:  Macro variable TAB_CONTROLLO resolves to Lgd17db.MOV_2020CONT
SYMBOLGEN:  Macro variable COLONNA_DATA resolves to DT_OPE
ERROR: Macro function %SUBSTR has too few arguments.
NOTE: Line generated by the macro function "SUBSTR".
1198  " then count(*) else 0 end as Numero_osservazioni from Lgd17db.MOV_2020CONT group by
      -------------------------------------------------------------------------------------
      49
1198! Data_Riferimento having calculated Numero_osservazioni; quit;endrsubmit;options mprint
1198! symbolgen mlogic;proc sql inobs=50; create table Tabella_sintesi as select distinct
SYMBOLGEN:  Macro variable COLONNA_DATA resolves to DT_OPE
WARNING: Argument 3 to macro function %SUBSTR is out of range.
SYMBOLGEN:  Macro variable DATA_RIF resolves to JAN2021
NOTE: Line generated by the macro variable "DATA_RIF".
1198  " end as Presenza_Mese_Controllo format=$4., case when _OPE= JAN2021
      --------------------------------------------------------------------
      49
SYMBOLGEN:  Macro variable TAB_CONTROLLO resolves to Lgd17db.MOV_2020CONT
MPRINT(CONTROLLO_MESI):  " then count(*) else 0 end as Numero_osservazioni from
Lgd17db.MOV_2020CONT group by Data_Riferimento having calculated Numero_osservazioni;
quit;endrsubmit;options mprint symbolgen mlogic;proc sql inobs=50; create table Tabella_sintesi
as select distinct MOV_2020CONT as Nome_Tabella,         &DATA_RIF as Data_Riferimento,
&sysdate9 as Data_Controllo, case when ,3,7)= &DATA_RIF then "si" else "no" end as
Presenza_Mese_Controllo format=$4., case when _OPE= JAN2021 and calculated
Presenza_Mese_Controllo= "si
MLOGIC(CONTROLLO_MESI):  Ending execution.
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS
             release.  Inserting white space between a quoted string and the succeeding
             identifier is recommended.

1199  endrsubmit;
NOTE: Remote submit to MYHOST complete.

in this part i m trying to apply macro functions %scan, %substr. But doesn't work

Thank you very much

Re: Help to define macro (Please)

Quentin — Thu, 23 Sep 2021 15:02:42 GMT

That log is helpful. Can you please show the macro code (macro definition) that works, and the macro code that does not work? So show both macro definitions, from %macro to %mend?

My guess is you should be using substr and scan, not the macro functions %substr and %scan.

So you'll want something like:

case when substr(put(&Colonna_data,date9.),3,7)= "&DATA_RIF" then "si" else "no" end as Presenza_Mese_Controllo format=$4.

That uses the regular SUBSTR function not %SUBSTR, because you want the substring of a text string where the value comes from a data set variable.

But seeing both macro definitions will help confirm that guess.

You're definitely getting closer. : )

Re: Help to define macro (Please)

Anto180788 — Thu, 23 Sep 2021 17:37:13 GMT

@Quentin

This is the code that works (without macro functions):

rsubmit;
options mprint symbolgen mlogic;
%macro Controllo_Mesi(Colonna_data,DATA_RIF,Tab_controllo);
proc sql;
create table Tabella_sintesi as 
select distinct scan("&Tab_controllo",2, '.') 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;

This is the code with macro functions that I would apply if it si possibile:

rsubmit;
options mprint symbolgen mlogic;
%macro Controllo_Mesi(Colonna_data,DATA_RIF,Tab_controllo);
proc sql;
create table Tabella_sintesi as 
select distinct %scan("&Tab_controllo",2, '.') 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

Re: Help to define macro (Please)

Tom — Thu, 23 Sep 2021 18:35:35 GMT

Why did you change the SUBSTR() function

case when (substr(put(&Colonna_data,date9.),3,7))="&DATA_RIF"
     then 'si' else 'no' 
end as Presenza_Mese_Controllo format=$4.

to %SUBSTR()?

case when (%substr(put(&Colonna_data,date9.),3,7))="&DATA_RIF"
     then 'si' else 'no' 
end as Presenza_Mese_Controllo format=$4.

So if we set COLLONNA_DATA to XXX the result of the %SUBSTR() function will be the string:

t(XXX,d

So the SAS code your macro is generating is going to be:

case when (t(XXX,d)="&DATA_RIF"
     then 'si' else 'no' 
end as Presenza_Mese_Controllo format=$4.

Which is just gibberish.

Re: Help to define macro (Please)

Tom — Thu, 23 Sep 2021 19:22:09 GMT

Let's look at the code the macro is generating and try to decide what it is doing.

Looks like you are getting a summary by the number of times the date in the variable named in parameter Colonna_data is within the year specified in the parameter DATA_RIF from the table named in the Tab_controllo parameter. Example call:

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

You should probably simplify the logic of the SAS code before trying to convert it into a macro.

You probably will want to make the variable with the SYSDATE into a numeric variable with date values.

"&sysdate9"d as Data_Controllo format=date9.

Why not just use the YEAR() function to get the year from the date variable?

year(&Colonna_data)=&DATA_RIF

Perhaps you meant to supply values like MAY2021 to indicate a particular month?

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

Do you want the new variable Data_Riferimento to be a character string? If so then do not attach a format with a width of 4 to a character variable with a length of only 2. There is no need to attach the $ format to character variables. If you want the variable to be long enough to store 4 bytes then set the LENGTH of the variable, not the display format.

"&DATA_RIF" as Data_Riferimento length=4

or a number?

&DATA_RIF as Data_Riferimento

To count how many times the date is from the year just sum the boolean expression. You should not need to use a sub-query to re-query the same table. That sounds very time consuming.

sum(calculated Presenza_Mese_Controllo='si') as Numero_osservazioni

You might also want to use -1 index in your SCAN() function to get the member name. That way users can specify WORK datasets using a one level name.

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

Re: Help to define macro (Please)

Quentin — Thu, 23 Sep 2021 19:53:01 GMT

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.

Re: Help to define macro (Please)

Anto180788 — Fri, 24 Sep 2021 07:12:15 GMT

@Tom

Thank you for reply

I just supposed to change the fucntion when I defined the macro. So, without macro you use simpe substr() and when you make a macro you use %substr. I understood that isn't the same result. So, to have the expected result, i can use only the simple substr().

Re: Help to define macro (Please)

Kurt_Bremser — Fri, 24 Sep 2021 07:18:52 GMT

You only use a macro function when the function has to do its work while the code is being compiled, BEFORE it actually runs.

Whenever a function needs to do its work while the resulting code executes, stay with the normal data step function.

Re: Help to define macro (Please)

Anto180788 — Fri, 24 Sep 2021 14:13:01 GMT

@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

Re: Help to define macro (Please)

Anto180788 — Fri, 24 Sep 2021 14:16:25 GMT

@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

Re: Help to define macro (Please)

Anto180788 — Fri, 24 Sep 2021 17:47:30 GMT

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

Re: Help to define macro (Please)

Tom — Sat, 25 Sep 2021 00:04:12 GMT

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;