Solved: Parse json file with Proc Groovy

BillJones · Posted 03-01-2015 02:08 AM

Hello everyone,

I'm trying to parse a json file with proc groovy. I believe that I correctly referenced the necessary jar files. However, I'm still getting errors. I'm running SAS 9.3 on Windows 7 Professional. Json file, code, and log are below. Any help or suggestions would be greatly appreciated.

Thanks very much,

Bill

json file

{"results":

[

{

"acct_nbr": 1234,

"firstName": "John",

"lastName": "Smith",

"age": 25,

"address": {

"streetAddress": "21 2nd Street",

"city": "New York",

"state": "NY",

"postalCode": "10021"

}

,

{

"acct_nbr": 3456,

"firstName": "Sam",

"lastName": "Jones",

"age": 32,

"address": {

"streetAddress": "25 2nd Street",

"city": "New Jersy",

"state": "NJ",

"postalCode": "10081"

}

]

}

Code:

dm 'clear log';

%let sourcefile=C:\json.txt;

%let outfile=c:\json.csv;

proc groovy;

add classpath="C:\Program Files (x86)\Groovy\Groovy-2.4.0\lib\groovy-all.2.4.0.jar";

add classpath="C:\Program Files (x86)\Groovy\Groovy-2.4.0\lib\opencsv-3.2.jar";

submit "&sourcefile" "&outfile";

import groovy.json.*

import au.com.bytecode.opencsv.CSVWriter

def input = new File(args[0]).text

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

def csvoutput = new FileWriter(args[1])

CSVWriter writer = new CSVWriter(csvoutput);

String[] header = new String[8];

header[0] = "results.acct_nbr";

header[1] = "results.firstName";

header[2] = "results.lastName";

header[3] = "results.age";

header[4] = "results.address.streetAddress";

header[5] = "results.address.city";

header[6] = "results.address.state";

header[7] = "results.address.postalCode";

writer.writeNext(header);

output.statuses.each {

String[] content = new String[8];

content[0] = it.results.acct_nbr.toString();

content[1] = it.results.firstName.toString();

content[2] = it.results.lastName.toString();

content[3] = it.results.age.toString();

content[4] = it.results.address.streetAddress.toString();

content[5] = it.results.address.city.toString();

content[6] = it.results.address.state.toString();

content[7] = it.results.address.postalCode.toString();

writer.writeNext(content)

}

writer.close();

endsubmit;

quit;

Log:

NOTE: The ADD CLASSPATH command completed.

ERROR: The SUBMIT command failed.

org.codehaus.groovy.control.MultipleCompilationErrorsException:

startup failed:

Script58.groovy: 2: unable to resolve class

au.com.bytecode.opencsv.CSVWriter

@ line 2, column 7.

import au.com.bytecode.opencsv.CSVWriter

^

Script58.groovy: 5: unable to resolve class JsonSlurper

@ line 5,

column 20.

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

^

2 errors

at

org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector

.java:296)

at

org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(Compila

tionUnit.java:860)

at

org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(Compilati

onUnit.java:521)

at

org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(Com

pilationUnit.java:497)

at

org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.ja

va:474)

at

groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:292)

at groovy.lang.GroovyShell.parseClass(GroovyShell.java:727)

at groovy.lang.GroovyShell.parse(GroovyShell.java:739)

at groovy.lang.GroovyShell.parse(GroovyShell.java:766)

at groovy.lang.GroovyShell.parse(GroovyShell.java:757)

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE GROOVY used (Total process time):

real time 0.01 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 34.53k

OS Memory 25028.00k

Timestamp 03/01/2015 01:19:49 AM

FriedEgg · Posted 03-02-2015 02:58 PM

Editor's note: Since this question was asked, a new JSON libname engine was added to SAS 9.4 Maint 4 (and SAS Viya). It's the simplest method for bringing JSON data into SAS. This PROC GROOVY method still works though, if you need to do other processing in Groovy.

The simplest possible explanation is that you are not setting up your classpath correctly. You should validate that your path to the groovy-all jar is correct. In my version groovy-all is in the embeddable subdirectory, not the lib. If the path is correct, you should validate the contents of groovy-all jar as it may be missing the groovy.json package. The following works for me on the Windows machine with SAS 9.3

filename cp temp;
proc groovy classpath=cp;
add classpath="C:\Program Files\Java\groovy-2.3.4\embeddable\groovy-all-2.3.4.jar";
/*or*/
/*
add classpath="C:\Program Files\Java\groovy-2.3.4\lib\groovy-2.3.4.jar";
add classpath="C:\Program Files\Java\groovy-2.3.4\lib\groovy-json-2.3.4.jar";
*/
submit parseonly;
import groovy.json.JsonSlurper
class MyJsonParser {
    def parseFile(path) {
     def jsonFile = new File(path)
    def jsonText = jsonFile.getText()
    def InputJSON = new JsonSlurper().parseText(jsonText)
        def accounts = []
        InputJSON.results.each{
            accounts << [
                    acct_nbr      : it.acct_nbr.toString(),
                    firstName     : it.firstName,
                    lastName      : it.lastName,
                    age           : it.age.toString(),
                    streetAddress : it.address.streetAddress,
                    city          : it.address.city,
                    state         : it.address.state,
                    postalCode    : it.address.postalCode
            ]
        }
        return accounts
    }
}
endsubmit;
submit parseonly;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;

public class MyJsonParser4Sas {
    public String filename = "";
    public void init() {
        MyJsonParser myParser = new MyJsonParser();
        accounts = myParser.parseFile(filename);
        iter = accounts.iterator();
    }
     public boolean hasNext() {
        return iter.hasNext();
    }
    public void getNext() {
        account = ((LinkedHashMap) (iter.next()));
    }
     public String getString(String k) {
        return account.get(k);
    }
     protected ArrayList accounts;
    protected Iterator iter;
    protected LinkedHashMap account;
}
endsubmit;
quit;
 options set=classpath "%sysfunc(pathname(cp,f))";
data accounts;
   attrib id            label="Account Index"  length=    8
          acct_nbr      label="Account Number" length=$  10
          firstName     label="First Name"     length=$  20
          lastName      label="Last Name"      length=$  30
          age           label="Age"            length=$   3
          streetAddress label="Street Address" length=$ 128
          city          label="City"           length=$  40
          state         label="State"          length=$   2
          postalCode    label="Postal Code"    length=$   5;
   dcl javaobj accounts("MyJsonParser4Sas");
   accounts.exceptiondescribe(1);
   accounts.setStringField("filename", "C:\\foo.json");
   accounts.callVoidMethod("init");
   accounts.callBooleanMethod("hasNext",rc);
   do id=1 by 1 while(rc);
      accounts.callVoidMethod("getNext");
   accounts.callStringMethod("getString", "acct_nbr", acct_nbr);
   accounts.callStringMethod("getString", "firstName", firstName);
   accounts.callStringMethod("getString", "lastName", lastName);
   accounts.callStringMethod("getString", "age", age);
   accounts.callStringMethod("getString", "streetAddress", streetAddress);
   accounts.callStringMethod("getString", "city", city);
   accounts.callStringMethod("getString", "state", state);
   accounts.callStringMethod("getString", "postalCode", postalCode);
      output;
   accounts.callBooleanMethod("hasNext",rc);
   end;
 
   drop rc;
run;

View solution in original post

FriedEgg · Posted 03-01-2015 11:10 AM

filename cp temp;

proc groovy classpath=cp;

add sasjar="groovy_2.1.3" version="2.1.3.0_SAS_20130517000930";

submit parseonly;

import groovy.json.JsonSlurper

class MyJsonParser {

def parseFile(path) {

def jsonFile = new File(path)

def jsonText = jsonFile.getText()

def InputJSON = new JsonSlurper().parseText(jsonText)

def accounts = []

InputJSON.results.each{

accounts << [

acct_nbr : it.acct_nbr.toString(),

firstName : it.firstName,

lastName : it.lastName,

age : it.age.toString(),

streetAddress : it.address.streetAddress,

city : it.address.city,

state : it.address.state,

postalCode : it.address.postalCode

]

}

return accounts

}

endsubmit;

submit parseonly;

import java.util.ArrayList;

import java.util.Iterator;

import java.util.LinkedHashMap;

public class MyJsonParser4Sas {

public String filename = "";

public void init() {

MyJsonParser myParser = new MyJsonParser();

accounts = myParser.parseFile(filename);

iter = accounts.iterator();

}

public boolean hasNext() {

return iter.hasNext();

}

public void getNext() {

account = ((LinkedHashMap) (iter.next()));

}

public String getString(String k) {

return account.get(k);

}

protected ArrayList accounts;

protected Iterator iter;

protected LinkedHashMap account;

}

endsubmit;

quit;

options set=classpath "%sysfunc(pathname(cp,f))";

filename json "/home/mkastin/Desktop/foo.json";

data accounts;

attrib id label="Account Index" length= 8

acct_nbr label="Account Number" length=$ 10

firstName label="First Name" length=$ 20

lastName label="Last Name" length=$ 30

age label="Age" length=$ 3

streetAddress label="Street Address" length=$ 128

city label="City" length=$ 40

state label="State" length=$ 2

postalCode label="Postal Code" length=$ 5;

dcl javaobj accounts("MyJsonParser4Sas");

accounts.exceptiondescribe(1);

accounts.setStringField("filename", "%sysfunc(pathname(json,f))");

accounts.callVoidMethod("init");

accounts.callBooleanMethod("hasNext",rc);

do id=1 by 1 while(rc);

accounts.callVoidMethod("getNext");

accounts.callStringMethod("getString", "acct_nbr", acct_nbr);

accounts.callStringMethod("getString", "firstName", firstName);

accounts.callStringMethod("getString", "lastName", lastName);

accounts.callStringMethod("getString", "age", age);

accounts.callStringMethod("getString", "streetAddress", streetAddress);

accounts.callStringMethod("getString", "city", city);

accounts.callStringMethod("getString", "state", state);

accounts.callStringMethod("getString", "postalCode", postalCode);

output;

accounts.callBooleanMethod("hasNext",rc);

end;

drop rc;

run;

1	1234	John	Smith	25	21 2nd Street	New York	NY	10021
2	3456	Sam	Jones	32	25 2nd Street	New Jersy	NJ	10081

A few notes:

1. The json as shared here is invalid and should be wrapped in "{}"

2. In windows you will need to modify the file's name in one of following ways

2a. escape the \ as \\, for example C:\json.txt would be C:\\json.txt

2b. use forward slash instead of backslash, for example C:\json.txt would be /C:/json.txt

2c. use a file uri specification ie. file://C:/json.txt

3. The add sasjar statement is specific to your version or SAS and OS, check your versioned jar repositiory as part of your installation or use a different groovy-all jar, as you had in OP

BillJones · Posted 03-01-2015 10:00 PM

Friedegg,

Thanks so much for your code and thoughts on this problem. I made your suggested changes, but still cannot get the code to run. Note I tried using both the groovy-all jar that was installed with SAS and a newer version. I got the same error in both cases.

-Bill

Log:

1661 dm 'clear log';

1662

1663 options mprint;

1664

1665 filename cp temp;

1666

1667 proc

1667! groovy classpath=cp;

NOTE: The ADD CLASSPATH command completed.

1668

1669 add classpath="C:\Program Files (x86)\Groovy\Groovy-2.4.0\lib\groovy-all.2.4.0.jar";

NOTE: The ADD CLASSPATH command completed.

1670

1671 submit parseonly;

1672 import groovy.json.JsonSlurper

1673 class MyJsonParser {

1674 def parseFile(path) {

1675 def jsonFile = new File(path)

1676 def jsonText = jsonFile.getText()

1677 def InputJSON = new JsonSlurper().parseText(jsonText)

1678 def accounts = []

1679

1680

1681 InputJSON.results.each{

1682 accounts << [

1683 acct_nbr : it.acct_nbr.toString(),

1684 firstName : it.firstName,

1685 lastName : it.lastName,

1686 age : it.age.toString(),

1687 streetAddress : it.address.streetAddress,

1688 city : it.address.city,

1689 state : it.address.state,

1690 postalCode : it.address.postalCode

1691 ]

1692 }

1693

1694

1695 return accounts

1696 }

1697 }

1698 endsubmit;

ERROR: The SUBMIT command failed.

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:

script14252651249521306453411.groovy: 1: unable to resolve class groovy.json.JsonSlurper

@ line 1,

column 1.

import groovy.json.JsonSlurper

^

1 error

at org.codehaus.groovy.control.ErrorCollector.failIfErrors(ErrorCollector.java:296)

at org.codehaus.groovy.control.CompilationUnit.applyToSourceUnits(CompilationUnit.java:860)

at org.codehaus.groovy.control.CompilationUnit.doPhaseOperation(CompilationUnit.java:521)

at org.codehaus.groovy.control.CompilationUnit.processPhaseOperations(CompilationUnit.java:497)

at org.codehaus.groovy.control.CompilationUnit.compile(CompilationUnit.java:474)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:292)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:263)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:207)

at groovy.lang.GroovyClassLoader.parseClass(GroovyClassLoader.java:217)

1699

1700

1701 submit parseonly;

1702 import java.util.ArrayList;

1703 import java.util.Iterator;

1704 import java.util.LinkedHashMap;

1705

1706

1707 public class MyJsonParser4Sas {

1708 public String filename = "";

1709

1710

1711 public void init() {

1712 MyJsonParser myParser = new MyJsonParser();

1713 accounts = myParser.parseFile(filename);

1714 iter = accounts.iterator();

1715 }

1716

1717

1718 public boolean hasNext() {

1719 return iter.hasNext();

1720 }

1721

1722

1723 public void getNext() {

1724 account = ((LinkedHashMap) (iter.next()));

1725 }

1726

1727

1728 public String getString(String k) {

1729 return account.get(k);

1730 }

1731

1732

1733 protected ArrayList accounts;

1734 protected Iterator iter;

1735 protected LinkedHashMap account;

1736 }

1737 endsubmit;

ERROR: The SUBMIT command failed.

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:

script1425265124971621978172.groovy: 11: unable to resolve class MyJsonParser

@ line 11, column 22.

MyJsonParser myParser = new MyJsonParser();

^

script1425265124971621978172.groovy: 11: unable to resolve class MyJsonParser

@ line 11, column 33.