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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.