Help to define macro (Please)

[Anto180788]

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

                -------

                22

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

                          ----

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

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

                          ----

                          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.

[PaigeMiller]

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.

[Anto180788]

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;

[PaigeMiller]

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?

[Anto180788]

@PaigeMillerThank you for your observations.

I looked that i missed to write quoting marks.

[Quentin]

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*/

[Anto180788]

@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

[Quentin]

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.

[Anto180788]

@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

[Quentin]

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. : )

[Anto180788]

@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

[Tom]

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.

[Anto180788]

@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().

[Kurt_Bremser]

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.

[Tom]

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