BookmarkSubscribeRSS Feed
DavidPhillips2
Rhodochrosite | Level 12

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.

13 REPLIES 13
novinosrin
Tourmaline | Level 20

i think you can't have a var name -- >     ' 'n

 

 

if not missing(department) then   call execute(cats('%callStudentBody(',nliteral(department),');'));

DavidPhillips2
Rhodochrosite | Level 12

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.

DavidPhillips2
Rhodochrosite | Level 12

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

.

novinosrin
Tourmaline | Level 20

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')),');'));

DavidPhillips2
Rhodochrosite | Level 12

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.

novinosrin
Tourmaline | Level 20

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

ballardw
Super User

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

ballardw
Super User

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.

 

DavidPhillips2
Rhodochrosite | Level 12

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;

 

DavidPhillips2
Rhodochrosite | Level 12

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.

DavidPhillips2
Rhodochrosite | Level 12

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;
 
DavidPhillips2
Rhodochrosite | Level 12

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;

DavidPhillips2
Rhodochrosite | Level 12

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1442 views
  • 2 likes
  • 3 in conversation