Zero padding fields pulled from SQL Server

bentleyj1 — Fri, 20 Jul 2012 21:26:43 GMT

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>

Re: Zero padding fields pulled from SQL Server

bentleyj1 — Fri, 20 Jul 2012 21:38:04 GMT

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."

Re: Zero padding fields pulled from SQL Server

PGStats — Fri, 20 Jul 2012 21:42:57 GMT

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

topic Re: Zero padding fields pulled from SQL Server in SAS Programming

Zero padding fields pulled from SQL Server

Re: Zero padding fields pulled from SQL Server

Re: Zero padding fields pulled from SQL Server