SAS and Salesforce - Your input needed

[JBailey]

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

[JBailey · Accepted solution]

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%%';

[Florent]

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

[JBailey]

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

[Florent]

Hi @JBailey,

No indeed, I meant SalesForce 

Regards,

Florent

[JBailey]

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

[mich1]

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

[JBailey]

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=ti...

Best wishes,

Jeff

@JBailey