topic Re: Connecting SAS to Salesforce in SAS Programming

Connecting SAS to Salesforce

RandoDando — Thu, 05 Aug 2021 13:19:17 GMT

I am using SAS EG which is using SAS version 9.4.3.0. How can I connect to a SalesForce database? I prefer to link to it so that I can explore the tables in SAS. I was granted an Admin account to SF so that I can see all which is there. Thanks

Re: Connecting SAS to Salesforce

jimbarbour — Thu, 05 Aug 2021 15:42:02 GMT

You would need to create a Libname. Assuming that whatever database Salesforce uses is compatible with SAS and that you have the proper drivers and such, you should then be able to browse just as you would SAS datasets.

The first thing I would want to know is: Do you have the proper SAS/Access licenses to read Salesforce? Then we have to look at the drivers you have available to you.

Can you run the below code and post the SAS log? Please use the indicated icon to post the log.

PROC SETINIT;
RUN;

PROC PRODUCT_STATUS;
RUN;

Jim

Re: Connecting SAS to Salesforce

RandoDando — Thu, 05 Aug 2021 17:56:15 GMT

Product expiration dates:
---Base SAS Software                                                                                   
---SAS/STAT                                                                                             
---SAS/GRAPH                                                                                           
---SAS/ETS                                                                                              
---SAS/FSP                                                                                              
---SAS/AF                                                                                               
---SAS/IML                                                                                              
---SAS/ASSIST                                                                                           
---SAS/CONNECT                                                                                      


---SAS OLAP Server                                                                                  
---MDDB Server common products                                                      
---SAS Integration Technologies                                                            
---SAS/Secure 168-bit                                                                              
---SAS/Secure Windows                                                                          
---Unused OLAP Slot                                                                                
---SAS Enterprise Guide                                                                          
---SAS/ACCESS Interface to Oracle                                                        
---SAS/ACCESS Interface to PC Files                                                      
---SAS/ACCESS Interface to ODBC                                                         
---SAS/IML Studio                                                                                     
---SAS Workspace Server for Local Access                                           
---SAS Workspace Server for Enterprise Access                                  
---SAS/ACCESS Interface to Sybase IQ                                                  
---High Performance Suite                                                                      
---SAS Add-in for Microsoft Excel                                                          
---SAS Add-in for Microsoft Outlook                                                     
---SAS Add-in for Microsoft PowerPoint                                              
---SAS Add-in for Microsoft Word                                                         
---SAS Visual Analytics Services                                                             

40         
41         PROC PRODUCT_STATUS;
42         run;

For Base SAS Software ...
   Custom version information: 9.4_M3
   Image version information: 
For SAS/STAT ...
   Custom version information: 14.1
For SAS/GRAPH ...
   Custom version information: 9.4_M3
For SAS/ETS ...
   Custom version information: 14.1
For SAS/FSP ...
   Custom version information: 9.4_M3
For SAS/AF ...
   Custom version information: 9.4_M3
For SAS/IML ...
   Custom version information: 14.1
For SAS/ASSIST ...
   Custom version information: 9.4
   Image version information: 
For SAS/CONNECT ...
   Custom version information: 9.4_M3
For SAS OLAP Server ...
   Custom version information: 9.4
For SAS Integration Technologies ...
   Custom version information: 9.4_M3
For High Performance Suite ...
   Custom version information: 2.2_M4
For SAS/ACCESS Interface to Oracle ...
   Custom version information: 9.4_M3
For SAS/ACCESS Interface to PC Files ...
   Custom version information: 9.4_M3
For SAS/ACCESS Interface to ODBC ...
   Custom version information: 9.4_M3

Looks like the engine for SF isn't there. Also tried the libname statement for SFORCE and got the error message that it couldn't find it.

Great. Looks like it's going to be the 'old fashioned' way of importing raw.

Re: Connecting SAS to Salesforce

jimbarbour — Thu, 05 Aug 2021 18:22:33 GMT

Well, now, hold on. There is still an option here. I see that you do have ODBC. We can use ODBC to connect -- if you have the driver on your SAS server.

When you ran SETINIT, which version of SAS did it say you have and what operating system? I need these two pieces of information:

Current version: 9.04.01M6P111518
Operating System:   WX64_SV .

It looks like you're on a Windows system. In Windows, we can use the ODBC Data Source Administrator to create a DSN for you to connect via. Basically, this tells SAS what driver to use for a particular type of database. In the screen shot below, you can see in the Driver column some of the drivers on my system. We need to see if there is one for Salesforce on your system.

Jim

Re: Connecting SAS to Salesforce

RandoDando — Thu, 05 Aug 2021 18:43:09 GMT

I am working on an application server. Wouldn't the driver have to be installed there? If so, then maybe a passthrough query would work. I've performed those, but Oracle and Sybase are a far cry from SF.

Re: Connecting SAS to Salesforce

jimbarbour — Thu, 05 Aug 2021 19:52:04 GMT

@RandoDando wrote:

I am working on an application server. Wouldn't the driver have to be installed there?

The driver needs to be on the same machine as SAS. It sounds like that is the case, but we won't know for sure until we look into the ODBC Data Source Administrator.

@RandoDando wrote:

maybe a passthrough query would work. I've performed those, but Oracle and Sybase are a far cry from SF.

Oracle and Sybase are definitely different than Salesforce. However, the nice thing about ODBC is that it does standardize the connection a bit, so there's that. I'm willing to help if you'd like to try. If not, then I think you'd have to export from SalesForce to csv or something and then import into SAS.

If you'd like to try, I would like to know these two pieces of information from Proc SetInit:

Current version: 9.04.01M6P111518
Operating System:   WX64_SV .

Jim

Re: Connecting SAS to Salesforce

RandoDando — Fri, 06 Aug 2021 12:49:47 GMT

I have another idea. From my prior post above showing the available add-ins and drivers for my SAS server, would I be able to connect to MS Access? I don't see "Access" in the list specifically, but does having other Office products there include that?

Re: Connecting SAS to Salesforce

jimbarbour — Fri, 06 Aug 2021 14:51:12 GMT

@RandoDando wrote:
I have another idea. From my prior post above showing the available add-ins and drivers for my SAS server, would I be able to connect to MS Access? I don't see "Access" in the list specifically, but does having other Office products there include that?

Yes, you should be able to connect to Access. If nothing else, you can do a Proc Import.

So, export from SF to Access and then read Access from SAS?

Jim