I don't have a SQL Server account but am working with a fellow who does. He's gone for the weekend so I can't have him show me that it really works.
He says that this query works to create a SAS data set of records extracted from a SQL Server table. My question concerns the way he's zero-filling the loan number, a text field. He's using a SQL Server approach to do it. Perhaps my understanding of SQL Pass-Through is incomplete, but I didn't know that you could use RDBMS-specific extensions in a CONNECT TO query like he does. Has anyone else seen this sort of thing?
PROC SQL;
CONNECT TO SQLSVR (DB="bigone" DSN="base" SCHEMA="dbo" UID="&ntuser" PWD="&ntpw");
CREATE TABLE Tdlq.ASC_loan_curr AS
SELECT * FROM CONNECTION TO SQLSVR
(SELECT
CASE WHEN LEN(ma.loan_num) = 6 THEN
RIGHT('0000000000' + CONVERT(VARCHAR,ma.client), 11) + RIGHT('0000000000' + CONVERT(VARCHAR,LTRIM(RTRIM(ma.loan_num))), 9)
ELSE
RIGHT('0000000000' + CONVERT(VARCHAR,ma.client), 10) + RIGHT('0000000000' + CONVERT(VARCHAR,LTRIM(RTRIM(ma.loan_num))), 10)
END AS mdss_acct_nbr
,ma.next_due_date
<snip>
Whoops, never mind. I see on page 5 of the SAS/Access Interfact to Relational Databases that the "The Pass-Through Facility accepts all the extensions to ANSI SQL that are provided by the DBMS."
Whoops, never mind. I see on page 5 of the SAS/Access Interfact to Relational Databases that the "The Pass-Through Facility accepts all the extensions to ANSI SQL that are provided by the DBMS."
Sending DBMS specific queries to a server is one on the main uses of SQL Pass-Through. Of course, it is generally much more comfortable to let SAS generate the DBMS specific queries. - PG
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.