Desktop productivity for business analysts and programmers

Automating ODBC Connection in SAS Enterprise Guide Based on Logged On User

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Automating ODBC Connection in SAS Enterprise Guide Based on Logged On User

Hi,

 

I'm trying to automate my data sourcing based on who is logged on at the moment (providing the logged on user has proper access permission to the DB server). The MyODBC connection in the sample below is an ODBC connection setup in the computer, with windows active directory userid login is the only one required to connect to the server. No individual user password is required as it's all based on group policy.

 

I'm currently using the following connection script in SAS program:

 

%let MyDatasrc=connect to odbc as MyDataSrc (required="dsn=MyODBC;uid=myuserid");

---------------------------------------------------------------------------------------------------------------------

proc sql;

 

&MyDataSrc;

 

Create table MyData as
Select * From Connection to MyDataSrc (
Select Field1, Field2
From SANDPIT.dbo.MyData
);

 

disconnect from MyDataSrc;

quit;

----------------------------------------------------------------------------------------

 

How do I replace "myuserid" with something like &SYSUSERID system variable that get the user.

Seems like if I change the part that defines the odbc connection:

(required="dsn=MyODBC;uid=myuserid"), SAS won't accept it as a valid option

 

Thank you

Rianto


Accepted Solutions
Solution
‎11-27-2016 07:11 PM
Respected Advisor
Posts: 3,062

Re: Automating ODBC Connection in SAS Enterprise Guide Based on Logged On User

What database are you connecting to? If it is SQL Server then you may be able to use Windows Authentication and avoid supplying a userid at all:

 

connect to odbc as MyDataSrc (required="dsn=MyODBC;Trusted_Connection=yes;")

View solution in original post


All Replies
Solution
‎11-27-2016 07:11 PM
Respected Advisor
Posts: 3,062

Re: Automating ODBC Connection in SAS Enterprise Guide Based on Logged On User

What database are you connecting to? If it is SQL Server then you may be able to use Windows Authentication and avoid supplying a userid at all:

 

connect to odbc as MyDataSrc (required="dsn=MyODBC;Trusted_Connection=yes;")
New Contributor
Posts: 2

Re: Automating ODBC Connection in SAS Enterprise Guide Based on Logged On User

Yes, it is SQL server and Windows Authentication. I've tried it and it works!

Thank's heaps...
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 163 views
  • 1 like
  • 2 in conversation