topic Re: SAS and Salesforce - Your input needed in SAS Data Management

SAS and Salesforce - Your input needed

JBailey — Thu, 05 Oct 2017 15:42:49 GMT

Hi,

If you are using SAS with Salesforce we could use your help. I am looking for advice. Feel free to contact me directly!

Here are a sample of the questions we need help with:

1/ Do you use custom objects in Salesforce?

2/ What is the typical size of a large collection of objects in Salesforce? How many records? Size per record?

3/ Do you write data to Salesforce?

4/ Can you provide same queries that you run from SAS?

Thanks in advance for your input.

Best wishes,

Jeff

Re: SAS and Salesforce - Your input needed

JBailey — Wed, 11 Oct 2017 19:13:12 GMT

I asked a couple of people this question via e-mail. Here is what I was told...

Here are a sample of the questions we need help with:
1) Do you use custom objects in Salesforce?

We access numerous custom objects (both objects and custom fields) in the SFDC implementation. From the perspective of the driver, there really is no difference between built in and custom.

2) What is the typical size of a large collection of objects in Salesforce?

I’m not clear on what you are looking for here. What are you defining as a collection of objects? How many records? We regularly pull tens of thousands to hundreds of thousands of record out in a matter of minutes. Size per record? Varies by object – many of the objects are quite wide, up to hundreds of fields. I would have to calculate size, not immediately available.

3) Do you write data to Salesforce?

Yes. We can write directly via the ODBC driver with SQL INSERT and UPDATE statements. We can also write via calls to the SFDC web services API.

4) Can you provide same queries that you run from SAS?

As I said above, we don’t run queries directly from SAS. We utilize Data Management Studio and the SFDC ODBC driver for data pulls from SFDC. Most of the queries are quite simple, such as

SELECT *

FROM SFORCE.ACCOUNT

WHERE SYS_LASTMODIFIEDDATE > '%%FILTER_LAST_UPDATE_DTTM%%';

Re: SAS and Salesforce - Your input needed

Florent — Wed, 06 Dec 2017 13:00:53 GMT

Hi JBailey,

Could you please share the syntax used to establish the connection to SharePoint using ODBC?

I am currently trying to find a way connecting our SAS base reports on SalesForce (in order to make use of the data available in the account and contact objects).

Thanks a lot in advance for the support!

Kind regards,
Florent

Re: SAS and Salesforce - Your input needed

JBailey — Thu, 07 Dec 2017 20:18:47 GMT

Hi @Florent,

Do you really mean Sharepoint? Either way, it is probably best to open a new topic because it will help people find the answer.

Best wishes,

Jeff

Re: SAS and Salesforce - Your input needed

Florent — Thu, 07 Dec 2017 20:33:12 GMT

Hi @JBailey,

No indeed, I meant SalesForce

Regards,

Florent

Re: SAS and Salesforce - Your input needed

JBailey — Wed, 13 Dec 2017 15:12:37 GMT

Hi @Florent,

Can you start another topic? I have an answer for you but I don't want to overload this topic.

Best wishes,

Jeff

Re: SAS and Salesforce - Your input needed

mich1 — Fri, 26 Jan 2018 19:32:53 GMT

SAS Scripts to Connect SAS to Salesforce

BASE SAS 9.4 M5 and DEVART ODBC WORKS - SEE EXAMPLE BELOW - SEE DEVART ON SALESFORCE APPS STORE

I wrote this comment for other SAS users who may need to do this...

Here is working SAS CODE AND SYNTAX:
/*ODBC TO SALESFORCE SANDBOX - FOLLOW INSTRUCTIONS FROM DOWNLOAD*/
libname SANDBOX odbc
datasrc='SANDBOX'
user=’SALESFORCE USER NAME'
password='PASSWORD'
schema=ALL
preserve_tab_names=yes
preserve_col_names=yes;

/*UPDATE TEXT FIELD USING ID*/
PROC SQL;
update SANDBOX.YOUROBJECT__c
set YOURFIELD__c = “I WAS HERE WITH SAS”
where ID = "YOURIDSTRINGHERE";
quit;

/*INSERT EXAMPLE*/
PROC SQL;
insert into SANDBOX.YOUROBJECT__c
set YOURPARENTRECORD__c = 'YOURSTRINGHSERE', NAME = "WHATEVER", YOURFIELD__c = "I WAS HERE WITH SAS";
quit;

Updates are a few tenths of second while inserts are 2-3 seconds (for me anyway)...Good solution if you just need to update a few thousand records

- Bread Crumbs and Circuses for all

Re: SAS and Salesforce - Your input needed

JBailey — Fri, 07 Jun 2019 15:03:44 GMT

Hi,

This is exciting. There is a new SAS/ACCESS product for Salesforce. This product requires SAS 9.4M6.

For more information take a look at the doc.

SAS Doc (Non-Relational) https://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=acnrdb&docsetTarget=titlepage.htm&locale=en

Best wishes,

Jeff

@JBailey