topic Re: What am I doing wrong with this macro? in SAS Programming

What am I doing wrong with this macro?

jpprovost — Thu, 16 Jan 2020 04:01:10 GMT

Hi there,

I want to extract a subset of a dataset based on a specific input variable called "region".

Here's my code, but I don't know why it doesn't works.


%macro liste_region (region=);

%if &region. = "Abitibi-Témiscamingue"	%then no_comte = "(1, 2, 101)" and out="AT";

FILENAME output "/folders/myfolders/&out..xlsx";



data work.membres_actifs_&out.;
set work.membres_actifs_circ;
where NoComteResidence in no_comte;
run;

data work.membres_anciens_&out.  ;
set work.membres_anciens_circ;
where NoComteResidence in no_comte;
run;

proc export data=work.membres_actifs_&out. 
outfile=output
dbms=xlsx replace;
sheet=membres_actifs;
run;

proc export data=work.membres_anciens_&out.
outfile=output
dbms=xlsx replace;
sheet=membres_anciens;
run;

%mend liste_region;

%liste_region(region="Abitibi-Témiscamingue");

It gives me the following error:

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         %liste_region(region="Abitibi-Témiscamingue");
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73           no_comte = "(1, 2, 101)" and out="AT"
              ________
              180
 WARNING: Apparent symbolic reference OUT not resolved.
 
 ERROR 180-322: Statement is not valid or it is used out of proper order.
 
 WARNING: Apparent symbolic reference OUT not resolved.
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73                                                                     data work.membres_actifs_&out.(drop=ComteMembre Titre
                                                                                                 _
                                                                                                 22
                                                                                                 200
 73       ! DateNaissance Ehnie Langue             M90 NoComteMembre NoComteResidence NoPLQ             P90 P1AN Reg Section
 73       !     Anciennete Cat:
 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73          cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run;
                                                                                            ________
                                                                                            22
                                                                                            76
 ERROR: Syntax error while parsing WHERE clause.
 
 ERROR 22-322: Syntax error, expecting one of the following: (, :.  
 
 ERROR 76-322: Syntax error, statement will be ignored.
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.MEMBRES_ACTIFS_ may be incomplete.  When this step was stopped there were 0 observations and 18 
          variables.
 WARNING: Data set WORK.MEMBRES_ACTIFS_ was not replaced because this step was stopped.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 
 WARNING: Apparent symbolic reference OUT not resolved.
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73                                                                                                         data
 73       ! work.membres_anciens_&out.  (drop=ComteMembre Titre DateNaissance Ehnie Langue M90 NoComteMembre NoComteResidence NoPLQ
                                 _
                                 22
                                 200
 73       ! P90 P1AN Reg Section Anciennete
 
 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.  
 
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.MEMBRES_ANCIENS_ may be incomplete.  When this step was stopped there were 0 observations and 18 
          variables.
 WARNING: Data set WORK.MEMBRES_ANCIENS_ was not replaced because this step was stopped.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73          Cat: cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run;
                                                                                                 ________
                                                                                                 22
                                                                                                 76
 ERROR: Syntax error while parsing WHERE clause.
 
 ERROR 22-322: Syntax error, expecting one of the following: (, :.  
 
 ERROR 76-322: Syntax error, statement will be ignored.
 
 ERROR: The value ABITIBI-TÉMISCAMINGUE is not a valid SAS member name.
 NOTE: The SAS System stopped processing this step because of errors.
 WARNING: The data set WORK.MEMBRES_ACTIFS_ may be incomplete.  When this step was stopped there were 0 observations and 48 
          variables.
 WARNING: Data set WORK.MEMBRES_ACTIFS_ was not replaced because this step was stopped.
 NOTE: DATA statement used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 
 WARNING: Apparent symbolic reference OUT not resolved.
 NOTE 137-205: Line generated by the invoked macro "LISTE_REGION".
 73          Cat: cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run;   proc sort
 73       ! data=work.membres_actifs_&out. ; by ComteResidence Nom Prenom; run; proc sort data=work.membres_anciens_&out. ; by
                                     _
                                     22
 73       ! ComteResidence Nom Prenom;
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT, 
               EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, 
               NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, NOUNIKEY, NOUNIKEYS, 
               NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS, NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED, 
               PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, UNIOUT, 
               UNIQUEOUT, WKNO, WORKNO.  
 
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73          Cat: cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run;   proc sort
 73       ! data=work.membres_actifs_&out. ; by ComteResidence Nom Prenom; run; proc sort data=work.membres_anciens_&out. ; by
                                     _
                                     200
 73       ! ComteResidence Nom Prenom;
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 WARNING: Apparent symbolic reference OUT not resolved.
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 
 WARNING: Apparent symbolic reference OUT not resolved.
 22: LINE and COLUMN cannot be determined.
 NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT, 
               EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, 
               NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, NOUNIKEY, NOUNIKEYS, 
               NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS, NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED, 
               PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, UNIOUT, 
               UNIQUEOUT, WKNO, WORKNO.  
 200: LINE and COLUMN cannot be determined.
 NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 ERROR 200-322: The symbol is not recognized and will be ignored.
 WARNING: Apparent symbolic reference OUT not resolved.
 
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.01 seconds
       
 
 
 WARNING: Apparent symbolic reference OUT not resolved.
 NOTE 137-205: Line generated by the invoked macro "LISTE_REGION".
 73          run;   proc export data=work.membres_actifs_&out.  outfile=output dbms=xlsx replace; sheet=membres_actifs; run;  proc
                                                         _
                                                         22
 73       ! export data=work.membres_anciens_&out. outfile=output dbms=xlsx replace; sheet=membres_anciens; run;
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, 
               REPLACE, TABLE, _DEBUG_.  
 
 NOTE: Line generated by the invoked macro "LISTE_REGION".
 73          run;   proc export data=work.membres_actifs_&out.  outfile=output dbms=xlsx replace; sheet=membres_actifs; run;  proc
                                                         _
                                                         200
 73       ! export data=work.membres_anciens_&out. outfile=output dbms=xlsx replace; sheet=membres_anciens; run;
 ERROR 200-322: The symbol is not recognized and will be ignored.
 
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 WARNING: Apparent symbolic reference OUT not resolved.
 NOTE: The SAS System stopped processing this step because of errors.
 
 
 WARNING: Apparent symbolic reference OUT not resolved.
 22: LINE and COLUMN cannot be determined.
 NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, 
               REPLACE, TABLE, _DEBUG_.  
 200: LINE and COLUMN cannot be determined.
 NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
 ERROR 200-322: The symbol is not recognized and will be ignored.
 NOTE: PROCEDURE EXPORT used (Total process time):
       real time           0.00 seconds
       cpu time            0.00 seconds
       
 WARNING: Apparent symbolic reference OUT not resolved.
 NOTE: The SAS System stopped processing this step because of errors.
 74         
 75         OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: What am I doing wrong with this macro?

Tom — Thu, 16 Jan 2020 05:23:23 GMT

The first error is because you tried to reference a macro variable name OUT, but your program never defined such a macro variable.

If you want to see what code your macro is generating turn on the MPRINT option.

This statement does not make any sense.

%if &region. = "Abitibi-Témiscamingue"	%then no_comte = "(1, 2, 101)" and out="AT";

If the test is true then the macro is going to generate this fragment of a SAS statement.

no_comte = "(1, 2, 101)" and out="AT"

It kind of looks like an assignment statement. But it is missing the semi-colon that marks the end of a statement.

It does not make any sense as an assignment since it appears to be setting NO_COMTE to result of some boolean expression.

Also you are tying to run it outside of any data step or proc where such an assignment statement could possibly work.

Were you intending to set some macro variables instead? Something like?

%if &region. = "Abitibi-Témiscamingue"	%then %do;
  %let no_comte = (1, 2, 101);
  %let out=AT;
%end;

If so then you should probably add either a %LOCAL or a %GLOBAL statement to your macro definition to make it clear whether you intend those macro variable to only live as long as the macro is running or not.

Re: What am I doing wrong with this macro?

Kurt_Bremser — Thu, 16 Jan 2020 05:24:35 GMT

Since macro processing is text only, no quotes are needed.

%macro liste_region (region=);

%if &region. = Abitibi-Témiscamingue
%then %do;
  %let no_comte = (1, 2, 101);
  %let out=AT;
%end;

also note that macro variables are set with %let, and each macro variable needs a separate statement.

Call the macro without quotes:

%liste_region(region=Abitibi-Témiscamingue)

Re: What am I doing wrong with this macro?

geoskiad — Thu, 16 Jan 2020 05:57:26 GMT

As stated above. And I think in your first 2 data steps you need to change no_comte to &no_comte. for it to work as expected in the WHERE statement. Similar as you have &out. And as Kurt said pay attention to the double quotes because they will be part of the macro var. So no_comte with double quotes will not work in the IN part of the WHERE statement if you want to look for values in 1, 2, 101.

Re: What am I doing wrong with this macro?

Kurt_Bremser — Thu, 16 Jan 2020 06:48:28 GMT

Thinking about it a little more, you will need some additional changes to make it run without problems:

%macro liste_region (region=);

%if &region. = Abitibi-Témiscamingue
%then %do;
  %let no_comte = NoComteResidence in (1, 2, 101);
  %let out=AT;
%end;
%else %do;
  %let no_comte=;
  %let out=non_AT;
%end;

FILENAME output "/folders/myfolders/&out..xlsx";

data work.membres_actifs_&out.;
set work.membres_actifs_circ;
where &no_comte.;
run;

data work.membres_anciens_&out.;
set work.membres_anciens_circ;
where &no_comte.;
run;

proc export
  data=work.membres_actifs_&out. 
  outfile=output
  dbms=xlsx
  replace
;
sheet=membres_actifs;
run;

proc export
  data=work.membres_anciens_&out.
  outfile=output
  dbms=xlsx
  replace
;
sheet=membres_anciens;
run;

%mend liste_region;

%liste_region(region=Abitibi-Témiscamingue);

as we need to make sure that the macro variables are present under all conditions, and correct filenames and where statements are created.

Re: What am I doing wrong with this macro?

jpprovost — Thu, 16 Jan 2020 14:06:26 GMT

@Tom wrote:

[...]

Were you intending to set some macro variables instead? Something like?
%if &region. = "Abitibi-Témiscamingue"	%then %do;
  %let no_comte = (1, 2, 101);
  %let out=AT;
%end;
If so then you should probably add either a %LOCAL or a %GLOBAL statement to your macro definition to make it clear whether you intend those macro variable to only live as long as the macro is running or not.

Hi Tom,

Exactly. I didn't know how to "express" myself of what I'm trying to achieve in a SAS-way (my apologies for this).

In fact, I want to create a macro that output some filtered (the filter is no_comte) lists based on a specific parameter (here is a region).

Then, inside the macro, I want to do :

If Region = "ABC" then no_comte = (1,2,3)

Because I want to use it later on a data step to filter it, like :

data I_want_this;

set orginal_lists;

where VARIABLE_TO_FILTER in (no_comte).

run;

Since the variable_to_filter is numeric, I want the variable no_comte to be a liste in the form (1, 2, 3)... I don't know if I'm clear enough ?

Re: What am I doing wrong with this macro?

Kurt_Bremser — Thu, 16 Jan 2020 14:17:43 GMT

You should start by creating a dataset that contains the region names and the corresponding NoComteResidence values. Then it's just a subselect in SQL:

data lookup;
input region :$30. NoComteResidence;
datalines;
Abitibi-Témiscamingue 1
Abitibi-Témiscamingue 2
Abitibi-Témiscamingue 101
;

proc sql;
create table work.membres_actifs_AT
as select *
from work.membres_actifs_circ
where NoComteResidence in (
  select NoComteResidence from lookup where region = "Abitibi-Témiscamingue"
);
quit;

If you expand the lookup table with the short codes, you can use it in the macro to create the necessary file- and dataset names.

Re: What am I doing wrong with this macro?

Tom — Thu, 16 Jan 2020 14:33:58 GMT

The macro processor is just a text replacement tool. So first figure out what SAS code you need to run. Code like:

where VARIABLE_TO_FILTER in (no_comte);

Will not work since it is comparing the dataset variable VARIABLE_TO_FILTER to the dataset variable NO_COMTE. If you did want to run that code then instead of IN operator you really should just use:

where VARIABLE_TO_FILTER = no_comte ;

Sounds like you want to run this code:

where VARIABLE_TO_FILTER in (1 2 101);

So you could get there a number of ways. You could set a macro variable to the list of values and replace the list with a reference to the macro variable.

%let list=1 2 101 ;
...
where VARIABLE_TO_FILTER in (&list.);

Or set the whole condition:

%let condition=VARIABLE_TO_FILTER in (1 2 101) ;
...
where &condition.;

What code do you want to run when the user specifies a different region? Do you want to just eliminate the WHERE statement? Use some other condition?

Re: What am I doing wrong with this macro?

jpprovost — Thu, 16 Jan 2020 15:04:47 GMT

I think the first one (set a macro variable to the list of values and replace the list with a reference to the macro variable) is the one that would work the best for me.

I tried to run the code with the modifications proposed by you :

%if &region. = "Abitibi-Témiscamingue" %then %do;
%let no_comte = (1, 2, 101);
%let out=AT;
%end;

all and it gives me the following error :

A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &region. = Abitibi-Témiscamingue.
The macro LISTE_REGION will stop executing.

Re: What am I doing wrong with this macro?

jpprovost — Thu, 16 Jan 2020 15:16:38 GMT

@jpprovost wrote:

I think the first one (set a macro variable to the list of values and replace the list with a reference to the macro variable) is the one that would work the best for me.

I tried to run the code with the modifications proposed by you :
%if &region. = "Abitibi-Témiscamingue" %then %do;
%let no_comte = (1, 2, 101);
%let out=AT;
%end;
all and it gives me the following error :
A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &region. = Abitibi-Témiscamingue.
The macro LISTE_REGION will stop executing.

I think I may have found the reason of the error.

In the name of the region Abitibi-Témiscamingue, there's a " - " which I think SAS interpret as a minus sign.

Instead, I tried to fix it with %str() in the macro and also in the parameter and it seems to work.

Am I doing it right ?

Re: What am I doing wrong with this macro?

Kurt_Bremser — Tue, 21 Jan 2020 06:32:22 GMT

You can make the comparison explicitly character:

%macro liste_region (region=);

%if "&region." = "Abitibi-Témiscamingue"
%then %do;
  %let no_comte = (1, 2, 101);
  %let out=AT;
%end;
%else %do;
  %let no_comte=;
  %let out=non_AT;
%end;

%put &=no_comte;
%put &=out;

%mend;

%liste_region(region=Abitibi-Témiscamingue)