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

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

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

2 REPLIES 2
SASKiwi
PROC Star

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;")
rs73
Calcite | Level 5
Yes, it is SQL server and Windows Authentication. I've tried it and it works!

Thank's heaps...

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

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
  • 2 replies
  • 2909 views
  • 1 like
  • 2 in conversation