SAS Access to Salesforce via REST API

Accessing Salesforce Objects using SAS without SAS Access to Salesforce or ODBC connection.

Application programming Interface (API) comes super useful when you want to bypass user interface to perform operation and integration into application you like.  REST API provided by Salesforce can come handy when you want to create, manipulate, and search data in Salesforce by sending HTTP requests to endpoints in Salesforce. 

Here we will see how we can utilize this REST API to connect SAS with Salesforce to query Objects, records, query results, metadata, and more. Obviously, this is not a traditional method but when you need to access data but don’t have SAS License to query SAS Access to salesforce this method comes handy.

What we need:

• Salesforce connected app
• Consumer Key and Consumer Secret
• Url to get OAuth token
• Bit knowledge of SOQL
• And SAS

Step 1: Create Salesforce connected App and get Consumer key and consumer secret.

 You can request one connected app to Salesforce Admin with access to Read and manage Object in Salesforce. There are many documents available online which tell you about how to create connected app. How to Create Connected Apps in Salesforce (microfocus.com)  or Enable OAuth Settings for API Integration (salesforce.com) . You can find many more useful urls which provide steps on how to create connected app and get consumer key and consumer secret.

Step 2: Write a Code to get OAuth token:

To get Token using REST API, salesforce developer forum was very useful to test cURL command before I use SAS.

Obtaining Access Token using cURL? – Salesforce Developer Community

It was simple post curl request to /service/ oauth2/token with grant type set to password and pass parameters to authenticate yourself. You might want to create a salesforce service id and use it once you test application.

curl -v https://xxxxxx-dev-ed.my.salesforce.com/services/oauth2/token -d "grant_type=password" -d "client_id=XXXX.XXXXXXXXXX" -d "client_secret=xxxxxxxxxxxxxxx" -d "[email protected]" --data-urlencode "password=mynotsogood@Password"

Once you get your access token convert curl to SAS Proc HTTP Code.

filename resp "%sysfunc(getoption(work))\token.json";
filename head "%sysfunc(getoption(work))\head.json";
proc http 
Method = "post"
URL =  "https://xxxxxxx-dev-ed.my.salesforce.com/services/oauth2/token"
    ct= "application/x-www-form-urlencoded"     
    in='[email protected]&password=xxxxxxx&grant_type=password&client_id=xxxx.xxxxxxxxxxxxxxx&client_secret=xxxxxxxxxx'    
	headerout=head 
	out=resp
	headerout_overwrite;
run;

libname resp json "%sysfunc(getoption(work))\token.json";

That was easy. Get token in macro variable

/*Put token in macro variable &token to reuse it*/
 
data _null_;
set resp.alldata;
if _n_=1 then call symputx("token",value);
run;

Now we have token means we are in game, let’s explore

Example 1: Getting list of objects in salesforce

For getting list of objects in salesforce, we first need is URL for REST API query. I was using API Version 48.  Remember to pass authorization token in header “Authorization”=”Bearer &token”

My Query URL was something like https://xxxxxxxx-dev-ed.my.salesforce.com/services/data/v48.0/sobjects/

/*Get total name of objects in salesforce by querying sobjects*/

%let path_json=%sysfunc(pathname(work));

 filename sobject "&path_json/sobject.json";

      proc http
           Method = "get"
           URL =  "https://xxxxxxxxx-dev-ed.my.salesforce.com/services/data/v48.0/sobjects/"
           out=sobject;
           headers "Authorization"="Bearer &token";
      run;

libname sobject json "&path_json/sobject.json";

I can see whole list of objects defined in salesforce in column named “name”.  I am only interested in Objects defined as queryable = 1.  Means these are the objects that user can query using SOQL.

 Example 2 : Get number of records from Object

We can Pass SOQL query along with url

services/data/v48.0/query/?q=SELECT+count(ID)+recordCount+FROM+&Object

/*  Get total number of records from objects  */
 /* url value has SOQL query to fetch object record*/
%let path_json=%sysfunc(pathname(work));
%let query_url = https://xxxxx-dev-ed.my.salesforce.com;
%let object = Account;

filename rec "&path_json\rec.json";
           proc http
                Method = "get"
                 URL =  "&query_url/services/data/v48.0/query/?q=SELECT+count(ID)+recordCount+FROM+&Object"
                 out=rec;
                 headers "Authorization"="Bearer &token";
           run;
libname rec json "&path_json\rec.json";

Example 3: Get first 200 records from Object

%let path_json=%sysfunc(pathname(work));
%let query_url = https://xxxxxxxx-dev-ed.my.salesforce.com;
%let object = Account;
filename rec "&path_json\rec.json";

proc http

	Method = "get"
	URL =  "&query_url/services/data/v55.0/query/?q=SELECT+FIELDS(ALL)+FROM+&Object+limit+200"
	out=rec;
	headers "Authorization"="Bearer &token";
	debug level=3;
run;
libname rec json "&path_json\rec.json";