topic Re: generate sas dataset using proc groovy-JSON in SAS Procedures

Create a SAS data set from JSON file using PROC GROOVY

HabAM — Thu, 19 Oct 2017 04:03:53 GMT

How do I parse the JSON file to a sas dataset using proc groovy:

here is my code-

proc groovy;
add classpath="C:\bin\groovy-2.4.12\lib\groovy-2.4.12.jar";
add classpath="C:\bin\groovy-2.4.12\lib\groovy-json-2.4.12.jar";
submit;

import groovy.json.JsonSlurper;
def json_string = (new File("C:\\Users\\test.json")).getText();
def jsonSlurper = new JsonSlurper()
def json = jsonSlurper.parseText(json_string);
def testDataList = []
json.each {
testDataList.add(jsonSlurper.parseText(it))
}
endsubmit;
quit;

Thank you

Re: generate sas dataset using proc groovy-JSON

Reeza — Thu, 19 Oct 2017 04:02:36 GMT

What version of SAS do you have? SAS 9.4 M4 has a JSON libname which may be easier.

In PROC GROOVY it’s a manual process to parse the items, and I think you need to understand the structure. It’s been a while since I’ve used it though.

Re: generate sas dataset using proc groovy-JSON

HabAM — Thu, 19 Oct 2017 06:04:47 GMT

I have 9.4 M2. I know the latest releason has a JSON lib. Thank you for the response.

Re: Create a SAS data set from JSON file using PROC GROOVY

hamishcarpenter — Mon, 25 Jun 2018 06:27:20 GMT

mehretua,

Unfortunately this is not possible directly. You will need to use groovy to parse the JSON then write to a file (CSV or similar) in a way that can be read in by SAS. This is probably the best approach for large sets of data.

It is possible to pass back simply key/value pairs from proc groovy into SAS by using the "exports" special variable which is used to populate SAS macro variables. This is used to pull out an OAuth token in the following example: https://blogs.sas.com/content/sascom/2013/12/12/how-to-import-twitter-tweets-in-sas-data-step-using-oauth-2-authentication-style/

/* Store bearer token in macro variable 'BEARER_TOKEN' */
 proc groovy classpath="groovy-all-2.2.1.jar";
    submit "&JSON_TWEET_FILE.";
      import groovy.json.JsonSlurper
      def input = new File(args[0]).text
      def result = new JsonSlurper().parseText(input)
      println "Recieved bearer token: ${result.access_token}"
      exports.putAt('BEARER_TOKEN',result.access_token)
    endsubmit;
 quit;

It is also possible to return a comma separated list of strings using a list in groovy. See http://support.sas.com/resources/papers/proceedings17/SAS0245-2017.pdf for an example.

I hope this helps. I've been exploring proc groovy on our SAS 9.4m4 linux environment. It looks suitable for pulling simple information from complex JSON. I'd be interested in seeing examples of writing more complex JSON.

Hamish