DATA Step, Macro, Functions and more

Using Call Execute With Nliteral Together

Reply
Valued Guide
Posts: 588

Using Call Execute With Nliteral Together

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.

PROC Star
Posts: 1,357

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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

 

 

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

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to novinosrin

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.

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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

.

PROC Star
Posts: 1,357

Re: Using Call Execute With Nliteral Together

[ Edited ]
Posted in reply to DavidPhillips2

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

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to novinosrin

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.

PROC Star
Posts: 1,357

Re: Using Call Execute With Nliteral Together

[ Edited ]
Posted in reply to DavidPhillips2

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

Super User
Posts: 13,084

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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

Super User
Posts: 13,084

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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.

 

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

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;

 

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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.

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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;
 
Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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;

Valued Guide
Posts: 588

Re: Using Call Execute With Nliteral Together

Posted in reply to DavidPhillips2

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;

 

Ask a Question
Discussion stats
  • 13 replies
  • 234 views
  • 2 likes
  • 3 in conversation