BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lapetitemaman
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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

 

 

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

The second method worked. Thank you so much!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 862 views
  • 1 like
  • 3 in conversation