BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bentleyj1
Quartz | Level 8

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>

1 ACCEPTED SOLUTION

Accepted Solutions
bentleyj1
Quartz | Level 8

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

2 REPLIES 2
bentleyj1
Quartz | Level 8

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

PGStats
Opal | Level 21

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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