I need to pass a value with an apostrophe into a marco using that I need to call using call execute.
What is wrong with my syntax that is causing:
proc sql;
create table departmentList as
select distinct nliteral(department) from mytable
quit;
data _null_;
set departmentList;
call execute(cats('%callStudentBody(',nliteral(department),');'));
run;
ERROR: The NLITERAL function cannot convert a blank string to a valid n-literal.
i think you can't have a var name -- > ' 'n
if not missing(department) then call execute(cats('%callStudentBody(',nliteral(department),');'));
novinosrin, I think you are right. I am modifying my code to remove the null. Then I will likely receive the error message I was thinking it was about for the apostrophe.
This is the type of error I meant to work though:
if not missing(department) then call execute(cats('%callStudentBody(',nliteral(department),');'));
ERROR: Literal contains unmatched quote
.
An easy fix if acceptable is keep alpha and digits only and then nliteral and call execute
if not missing(department) then call execute(cats('%callStudentBody(',nliteral(department),');'));
modified to:
compress(department, ,'kad');
if not missing(department) then call execute(cats('%callStudentBody(',nliteral(compress(department, ,'kad')),');'));
Will this change the value of the department that I am sending? I’m trying to send one department at a time so that I can run a where clause in the macro.
You are right. I am sorry. Without knowing the data, i should be recommending such a thing.
How about this gamble with %superq
if not missing(department) then call execute %nrstr(cats('%callStudentBody(',%superq(nliteral(department)),');'));
I would request @ballardw to opine on the %superQ above and correct if he doesn;t mind
EDITED further at 3:24 CDT:
Ok, the above won't work i think.
At the time of execution of %callstudentbody macro , the macro processor with resolve some macro variable to the nliteral department value. That macro reference will prolly be something like &dept.
I believe that's when the unmatched quote is perhaps causing the problem. If this is right, the %superq will be very helpful to change &dept to %superq(dept);
Either way, best is to wait for responses from others
@DavidPhillips2 wrote:
Will this change the value of the department that I am sending? I’m trying to send one department at a time so that I can run a where clause in the macro.
Can you show us an example of a use of the macro without call execute that works as intended.
Or the code of the macro called and maybe some example data.
Or at least the macro where clause and some surrounding code.
I am not a fan of letting a function create a variable name that I haven't checked to create valid values.
And since you seem to be creating multiple variable names perhaps this goes back at root to data structure problem where you should have a single variable "department" that takes on the values of the department names like "Sporting goods" or "Women's Clothing" and then process with a BY group process to create the results for each department.
Maybe exclude the possibility of blank value prior:
proc sql; create table departmentList as select distinct nliteral(department) from mytable where not missing(department) ; quit;
I find that sometimes working with Call execute it is better to create a longish string variable to build the argument for call execute so I can check the resolution of the string functions with the added complication of the actual call execute and especially if macro calls are involved.
Such as
data work.junk; department='some dept'; length str $ 200; str =cats('%callStudentBody(',nliteral(department),')'); /*call execute(str);*/ run;
I can look at STR and see if the call is as desired. Then test it one time in this case by uncommenting the call execute.
I'm trying to figure out how to pass the nliteral correctly between macros.
The department list contains the value: Arts-Qatar Dean's Office
/*set the where clauses that are used in the body.*/ %macro setDepartmentClause(curDepartment); %let cdepartmentClause = %str(and c_department_desc =&curDepartment); %mend; /*creates the acutal report. The tables for the report are created using dynamic where clauses*/ %macro Body(curDepartment); %setDepartmentClause(&curDepartment); /*here i'm running into "ERROR: Literal contains unmatched quote." because I am passing the nliteral incorrectly*/ %mend; /*call the macro body and loop it with each department */ data _null_; set departmentList; length str $ 200; str =cats('%Body(',nliteral(department),')'); call execute(str); run;
I had a put statement that threw the error i just posted. I took it out to look at the errors around the dynamic where clause and noticed it was a little unexpected.
The part of the dynamic where clause I am focusing on is:
and department ="Arts-Qatar Dean's Office"N
with the message:
ERROR: The following columns were not found in the contributing tables: Arts-Qatar Dean''s Office.
I'm not sure why the log states that the quoted text is a column.
Maybe this will make more sense. I'm trying to run this:
%Global cdepartmentClause; proc sql; create table departmentList (department varchar(100)); insert into departmentList (department) values ("Arts-Qatar Dean's Office"); create table enrollment (c_department_desc varchar(100)); insert into enrollment (c_department_desc) values ("Arts-Qatar Dean's Office"); quit; /*set the where clauses that are used in the body.*/ %macro setDepartmentClause(curDepartment); %let cdepartmentClause = %str(c_department_desc =&curDepartment); %mend; /*creates the acutal report. The tables for the report are created using dynamic where clauses*/ %macro Body(curDepartment); %setDepartmentClause(&curDepartment); data outputResults; set enrollment; /*here i'm running into "ERROR: Literal contains unmatched quote." because I am passing the nliteral incorrectly*/ where &cdepartmentClause.; run; %mend; /*call the macro body and loop it with each department */ data _null_; set departmentList; length str $ 200; str =cats('%Body(',nliteral(department),')'); call execute(str); run;
SAS replied with this solution:
%Global cdepartmentClause;
proc sql;
create table departmentList (department varchar(100));
insert into departmentList (department) values ("Arts-Qatar Dean's Office");
create table enrollment (c_department_desc varchar(100));
insert into enrollment (c_department_desc) values ("Arts-Qatar Dean's Office");
quit;
/*set the where clauses that are used in the body.*/
%macro setDepartmentClause(curDepartment);
%let cdepartmentClause =%str(c_department_desc ="&curDepartment");
%mend;
/*creates the acutal report. The tables for the report are created using dynamic where clauses*/
%macro Body(curDepartment);
%setDepartmentClause(&curDepartment);
data outputResults; set enrollment;
/*here i'm running into "ERROR: Literal contains unmatched quote." because I am passing the nliteral incorrectly*/
where &cdepartmentClause;
run;
%mend;
/*call the macro body and loop it with each department */
data _null_;
set departmentList;
length str $ 200;
str =cats('%Body(%bquote(',department,'))');
call execute(str);
run;
A complication occurs when the department has & in the name.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was:
&curDepartment eq
%Global cdepartmentClause; proc sql; create table departmentList (department varchar(100)); insert into departmentList (department) values ("Counseling & Special Education"); create table enrollment (c_department_desc varchar(100)); insert into enrollment (c_department_desc) values ("Counseling & Special Education"); quit; /*set the where clauses that are used in the body.*/ %macro setDepartmentClause(curDepartment); /*I need to check if the department is blank for looping purposes*/ %if &curDepartment eq %then %do; %let cdepartmentClause = %str(); %end; %else %do; %let cdepartmentClause =%str(c_department_desc ="&curDepartment"); %end; %mend; /*creates the acutal report. The tables for the report are created using dynamic where clauses*/ %macro Body(curDepartment); %setDepartmentClause(&curDepartment); data outputResults; set enrollment; where &cdepartmentClause; run; %mend; %Body(); /*call the macro body and loop it with each department */ data _null_; set departmentList; length str $ 200; str =cats('%Body(%bquote(',department,'))'); call execute(str); run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.