Help using Base SAS procedures

Parse json file with Proc Groovy

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Parse json file with Proc Groovy

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.

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


Accepted Solutions
Solution
‎03-02-2015 02:58 PM
Trusted Advisor
Posts: 1,300

Re: Parse json file with Proc 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


All Replies
Trusted Advisor
Posts: 1,300

Re: Parse json file with Proc Groovy

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;

         

11234JohnSmith2521 2nd StreetNew YorkNY10021
23456SamJones3225 2nd StreetNew JersyNJ10081

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

Frequent Contributor
Posts: 92

Re: Parse json file with Proc Groovy

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.

           MyJsonParser myParser = new MyJsonParser();

                                   ^

2 errors

     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)

1738

1739

1740  quit;

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

NOTE: PROCEDURE GROOVY used (Total process time):

      real time           0.04 seconds

      cpu time            0.01 seconds

1741

1742

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

1744  filename json "/C:/json.txt";

1745

1746

1747  data accounts;

1748     attrib id            label="Account Index"  length=    8

1749            acct_nbr      label="Account Number" length=$  10

1750            firstName     label="First Name"     length=$  20

1751            lastName      label="Last Name"      length=$  30

1752            age           label="Age"            length=$   3

1753            streetAddress label="Street Address" length=$ 128

1754            city          label="City"           length=$  40

1755            state         label="State"          length=$   2

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

1757

1758

1759     dcl javaobj accounts("MyJsonParser4Sas");

1760     accounts.exceptiondescribe(1);

1761

1762

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

1764

1765

1766     accounts.callVoidMethod("init");

1767

1768

1769     accounts.callBooleanMethod("hasNext",rc);

1770     do id=1 by 1 while(rc);

1771        accounts.callVoidMethod("getNext");

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

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

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

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

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

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

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

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

1780        output;

1781     accounts.callBooleanMethod("hasNext",rc);

1782     end;

1783

1784     drop rc;

1785  run;

ERROR: Could not find class MyJsonParser4Sas at line 1759 column 24.  Please ensure that the

       CLASSPATH is correct.

ERROR: DATA STEP Component Object failure.  Aborted during the EXECUTION phase.

java.lang.ClassNotFoundException: MyJsonParser4Sas

     at java.net.URLClassLoader$1.run(Unknown Source)

     at java.security.AccessController.doPrivileged(Native Method)

     at java.net.URLClassLoader.findClass(Unknown Source)

     at java.lang.ClassLoader.loadClass(Unknown Source)

     at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:674)

     at groovy.lang.GroovyClassLoader.loadClass(GroovyClassLoader.java:773)

     at java.lang.ClassLoader.loadClass(Unknown Source)

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

WARNING: The data set WORK.ACCOUNTS may be incomplete.  When this step was stopped there were 0

         observations and 9 variables.

WARNING: Data set WORK.ACCOUNTS was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

Solution
‎03-02-2015 02:58 PM
Trusted Advisor
Posts: 1,300

Re: Parse json file with Proc 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;

Frequent Contributor
Posts: 92

Re: Parse json file with Proc Groovy

FriedEgg,

It was the classpath.  I added groovy-2.4.0.jar and groovy-json-2.4.0.jar separately, and the code runs!  Thanks so much for your assistance in this matter. 

Note the json parser seems to execute quickly.  Now that you've given me a working  template, I'll try to compare json with xml to see which is faster.

Thanks again.

Bill

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 3505 views
  • 5 likes
  • 2 in conversation