No observations using SAS macro ole db connection

[lapetitemaman]

Hi everyone,

I am trying to pull some data from an internal database through OLE DB connection using a SAS macro. Below is my code:

%macro list(Form, name);

proc sql;

connect to oledb (init="Provider=xxxxx; Password=xxxxx; Persist Security Info=True; Usder ID=xxxx; Initial Catalog=xxxx; Data Source=xxx");

create table &Form as 

select * from connection to oledb(

select distinct(t1.Item)

from Table1 as t1

cross join Table2 as t2

where t1.ID=t2.ID and t2.exam_version='&name'

group by t1.Item;);

quit;

%mend list;

%list(Form1,Math_TC1)

The code ran but resulted in 0 observations with no error message. However, if I replaced the &name in the macro with the actual name (i.e., Math_TC1), the code would run correctly. Any pointers what the issue may be and how to modify my code?

Thanks a lot!

[ballardw]

Macro variables do not resolve inside single quotes.

So in this

where t1.ID=t2.ID and t2.exam_version='&name'

the comparison was to the literal value &name which would almost certainly not appear in your data.

try

where t1.ID=t2.ID and t2.exam_version="&name"

[lapetitemaman]

I have tried to use double quotes instead of single quotes and received an error message.

ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be
completed.: Statement(s) could not be prepared.: Invalid column name 'xxx'. 

[Quentin · Accepted solution]

maybe try:

where t1.ID=t2.ID and t2.exam_version=%str(%')&name%str(%')

Or you could refactor it to pass the quotes in the value for NAME when you call the macro:

%macro list(Form, name);

proc sql;
connect to oledb (init="Provider=xxxxx; Password=xxxxx; Persist Security Info=True; Usder ID=xxxx; Initial Catalog=xxxx; Data Source=xxx");
create table &Form as 
select * from connection to oledb(
select distinct(t1.Item)
from Table1 as t1
cross join Table2 as t2
where t1.ID=t2.ID and t2.exam_version=&name   /*no quotes here*/
group by t1.Item;);
quit;

%mend list;

%list(Form1,'Math_TC1')  /*added quotes here*/

[lapetitemaman]

The second method worked. Thank you so much!