topic Re: No observations using SAS macro ole db connection in SAS Programming

No observations using SAS macro ole db connection

lapetitemaman — Wed, 09 Feb 2022 20:40:09 GMT

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!

Re: No observations using SAS macro ole db connection

ballardw — Wed, 09 Feb 2022 20:55:29 GMT

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"

Re: No observations using SAS macro ole db connection

lapetitemaman — Wed, 09 Feb 2022 21:04:12 GMT

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

Re: No observations using SAS macro ole db connection

Quentin — Wed, 09 Feb 2022 21:14:34 GMT

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*/

Re: No observations using SAS macro ole db connection

lapetitemaman — Wed, 09 Feb 2022 21:26:58 GMT

The second method worked. Thank you so much!