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 ®ion. = "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;
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 ®ion. = "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 ®ion. = "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.
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 ®ion. = "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 ®ion. = "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.
@Tom wrote:
[...]
Were you intending to set some macro variables instead? Something like?
%if ®ion. = "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 ?
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.
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?
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 ®ion. = "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: ®ion. = Abitibi-Témiscamingue.
The macro LISTE_REGION will stop executing.
@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 ®ion. = "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: ®ion. = 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 ?
You can make the comparison explicitly character:
%macro liste_region (region=);
%if "®ion." = "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)
Since macro processing is text only, no quotes are needed.
%macro liste_region (region=);
%if ®ion. = 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)
Thinking about it a little more, you will need some additional changes to make it run without problems:
%macro liste_region (region=);
%if ®ion. = 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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: