see, it’s hip to be square
‘cuz SAS has a new PROC that’s GROOVY
-Chris Hemedinger, Poetry on our own terms

These days I played Proc Groovy (new in SAS 9.3) for a while because Groovy natively supports JSON (JavaScript Object Notation) data format. I downloaded much JSON data in the past few weeks(Github archive for example).

——–I’m a line separator ———

DO wish SAS can also support JSON in the following release, in a way similar to the existing XML Libname engine, ODS XML markup and SAS XML Mapper!

_UPDATE 20121030: I submitted a ticket on SASware Ballot on this idea and get a feedback that SAS will support JSON since 9.4, both reading and writing. That’s cool._

——–I’m a line separator end———

Groovy is a dynamic language running in a JVM (Java Virtual Machine) and looks like a lightweight version of JAVA. Don’t know why Groovy was chosen but it is nice to have (at least to parse JSON data) and it’s in Base SAS!

Set UP

Back to Proc Groovy. To make it work, you should first set a JDK(Java Development Kit; or at least a JVM, also called JRE, Java Runtime Environment) and install Groovy. I test in a Windows 7 machine with SAS 9.3:

Install a JDK (or JVM). Get a proper version for your machine. I have a JDK7 installed in C:Program FilesJavajdk1.7.0_09. By the way, if your SAS 9.3 works well in a64 bit of Windows 7, there must be one JRE in C:Program Files (x86)Javajre1.6.0_24.
Set up the windows environment variable for JRE. First, create a system variable JAVA_HOME with value of C:Program FilesJavajdk1.7.0_09, then put %JAVA_HOME%bin to the existing system variable Path. If you use a JVM, replace the value of JAVA_HOME as C:Program FilesJavajre7 or other proper directory.
Install Groovy using the latest version (current v2.05). Accept all the default settings and it will set the environment variable for Groovy. You will have the Groovy installed in C:Program Files (x86)GroovyGroovy-2.0.5.
Locate the sasv9.cfg file in C:Program FilesSASHomeSASFoundation9.3nlsen, find option –JREOPTIONS and add a line inside

-Dtkj.app.class.path=C:Program Files (x86)GroovyGroovy-2.0.5embeddablegroovy-all-2.0.5.jar

That’s it.

Hello World in Proc Groovy

proc groovy;
    submit;
        def name=’World’;
        println “Hello $name!”
    endsubmit;
quit;

Read JSON data

Use a simple example from _JSON in wikipedia (save it to a file, test.json)_:

{
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [
        {
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}

The following codes simple demonstrate how to:

read JSON file
print JSON file
extract JSON values
output values into SAS macro variables

options nosource;

proc groovy;

submit;

import groovy.json.*

def input=new File(‘a:testtest.json’).text

def output = new JsonSlurper().parseText(input)

println output
println ""

output.each {println it}

println “”

println output.address.streetAddress

println “Street Address: $output.address.streetAddress”

<p>
  &#160;&#160;&#160;&#160;&#160;&#160;&#160; println output.address["streetAddress"]&#160;&#160;&#160;&#160;&#160;&#160;&#160; </font>
</p>

<p>
  <br /><font face="Courier New">&#160;&#160;&#160;&#160;&#160;&#160;&#160; exports = [fName1:output[&#8216;firstName&#8217;]]&#160;&#160;&#160; <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; exports.put(&#8216;fName2&#8217;, output[&#8216;firstName&#8217;])&#160; <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; endsubmit; </p> 

  <p>
    quit;</font>
  </p>

  <p>
    <font face="Courier New">%put fName1: &fName1;<br /> <br />%put fName2: &fName2;</font>
  </p>

  <pre><font size="1" face="Verdana">The output in LOG window:</font></pre>

  <pre><a href="http://www.jiangtanghu.com/blog/wp-content/uploads/2012/10/JsoninSAS_Groovy.png"><img style="background-image: none; border-right-width: 0px; margin: 3px auto 5px; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="JsoninSAS_Groovy" border="0" alt="JsoninSAS_Groovy" src="http://www.jiangtanghu.com/blog/wp-content/uploads/2012/10/JsoninSAS_Groovy_thumb.png" width="515" height="308" /></a></pre>

  <p align="left">
    <font size="1"></font>
  </p>

  <p>
    Happy Groovying!
  </p>

  <h1>
    <font style="font-weight: bold">References</font>
  </h1>

  <p>
    <a href="http://blog.willmakeaplan.com/?p=18" target="_blank"><font size="1"><em>Parse JSON in SAS with Groovy – part 2</em></font></a><em>&#160;</em><font size="1">by Simon Dawson</font>
  </p>

  <p>
    <a href="http://groovy.codehaus.org/Documentation"><font size="1">http://groovy.codehaus.org/Documentation</font></a>
  </p>

  <p>
    <a href="http://support.sas.com/documentation/cdl/en/proc/65145/HTML/default/viewer.htm#p1x8agymll9gten1ocziihptcjzj.htm" target="_blank"><font size="1"><em>Overview: GROOVY Procedure</em></font></a><em>&#160;</em><font size="1">in support.sas.com</font>
  </p>

Hello Groovy in SAS 9.3

Set UP

Hello World in Proc Groovy

Read JSON data