topic Re: Using Call Execute With Nliteral Together in SAS Programming

Using Call Execute With Nliteral Together

DavidPhillips2 — Thu, 10 May 2018 19:11:39 GMT

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.

Re: Using Call Execute With Nliteral Together

novinosrin — Thu, 10 May 2018 19:14:17 GMT

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

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

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Thu, 10 May 2018 19:17:52 GMT

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.

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Thu, 10 May 2018 19:52:44 GMT

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

Re: Using Call Execute With Nliteral Together

novinosrin — Thu, 10 May 2018 20:04:24 GMT

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

Re: Using Call Execute With Nliteral Together

ballardw — Thu, 10 May 2018 20:06:50 GMT

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.

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Thu, 10 May 2018 20:08:48 GMT

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.

Re: Using Call Execute With Nliteral Together

novinosrin — Thu, 10 May 2018 20:23:55 GMT

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

Re: Using Call Execute With Nliteral Together

ballardw — Thu, 10 May 2018 21:58:24 GMT

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

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Wed, 16 May 2018 14:31:58 GMT

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;

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Wed, 16 May 2018 15:05:24 GMT

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.

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Fri, 18 May 2018 17:10:36 GMT

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;

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Fri, 18 May 2018 17:40:39 GMT

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;

Re: Using Call Execute With Nliteral Together

DavidPhillips2 — Thu, 24 May 2018 20:29:38 GMT

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;