How to establish SQL server connection string in SAS using the following credentials?
Stored Procedure: SSSP_RES_V12_Load
Thank you
PROC SQL;
Connection string to exec stored procedure that works..
CONNECT TO ODBC(NOPROMPT="DRIVER={ODBC Driver 13 for SQL Server}; SERVER=&svr_nm.; database=&DB_nm.; Trusted_Connection=yes;");
CREATE TABLE WORK.Daily_copy AS
SELECT *
FROM CONNECTION TO ODBC (exec &sp.);
DISCONNECT FROM ODBC;
quit;
This Oracle example can be easily modified for SQL Server: https://support.sas.com/kb/18/350.html
Which SAS/ACCESS product are you using? SAS/ACCESS to SQL Server or SAS/ACCESS to ODBC?
Please note you can't read a result set while running a stored procedure. The workaround is to have the stored procedure create a temporary table then read that in a following PASSTHRU query with a SELECT statement.
PROC SQL;
Connection string to exec stored procedure that works..
CONNECT TO ODBC(NOPROMPT="DRIVER={ODBC Driver 13 for SQL Server}; SERVER=&svr_nm.; database=&DB_nm.; Trusted_Connection=yes;");
CREATE TABLE WORK.Daily_copy AS
SELECT *
FROM CONNECTION TO ODBC (exec &sp.);
DISCONNECT FROM ODBC;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.