BookmarkSubscribeRSS Feed
HabAM
Quartz | Level 8

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

HabAM
3 REPLIES 3
Reeza
Super User

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
Quartz | Level 8

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

HabAM
hamishcarpenter
Fluorite | Level 6

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

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1521 views
  • 0 likes
  • 3 in conversation