DATA Step, Macro, Functions and more

Zero padding fields pulled from SQL Server

Accepted Solution Solved
Reply
Contributor
Posts: 69
Accepted Solution

Zero padding fields pulled from SQL Server

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>


Accepted Solutions
Solution
‎07-20-2012 05:38 PM
Contributor
Posts: 69

Re: Zero padding fields pulled from SQL Server

Posted in reply to bentleyj1

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

View solution in original post


All Replies
Solution
‎07-20-2012 05:38 PM
Contributor
Posts: 69

Re: Zero padding fields pulled from SQL Server

Posted in reply to bentleyj1

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

Respected Advisor
Posts: 4,932

Re: Zero padding fields pulled from SQL Server

Posted in reply to bentleyj1

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

PG
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 457 views
  • 0 likes
  • 2 in conversation