Specifying Data Link Properties for an SQL query

chris_e — Wed, 21 Sep 2016 23:59:23 GMT

I have created a macro that creates a new table based on an SQL procedure that pulls data from some SQL tables. This macro is below and works without any problems as expected:

%macro Pull_Data_from_db(database, server, portid, tblname);

	%let libraryname = db;
	
proc sql;
	
	connect to oledb (init_string="Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=&database.; data source=&server." schema=dbo);
			create table &tblname. as select * from connection to oledb (

	SQL code;
quit;
%mend;

I next want to create a macro that will take this macro putting it into a loop and run it on multiple tables. So far I have put the function into a new function and tried running it in that function and rather than just connecting I get a pop up asking to specify Data Link Properties.

%macro Pull_Multi_tables_from_db(database, server, portid, tblname);

	%Pull_Data_from_EDM(database, server, portid, tblname);

%mend;

Is there a reason my macro is able to connect properly when run by itself but not when put into a second macro?

Re: Specifying Data Link Properties for an SQL query

chris_e — Thu, 22 Sep 2016 00:15:19 GMT

Sorry just realised I needed to put & on the front of the variables and . on the end in the second function.

%macro Pull_Multi_tables_from_db(database, server, portid, tblname);

	%Pull_Data_from_EDM(&database., &server., &portid., &tblname.);

%mend;

topic Specifying Data Link Properties for an SQL query in SAS Procedures

Specifying Data Link Properties for an SQL query

Re: Specifying Data Link Properties for an SQL query