Create a SAS data set from JSON file using PROC GROOVY

[HabAM]

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

[Reeza]

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. 

[HabAM]

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

[hamishcarpenter]

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

/* 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