About BillM_SAS

BillM_SAS · ‎11-13-2018

All software has limitations for various reasons. If you are unhappy with this limitation, you could submit your idea as a SASware Ballot entry.

BillM_SAS · ‎11-13-2018

As Chris notes, you could use multiple PROC JSON statements to produce multiple files each containing a single JSON object. This code will produce a single JSON object as you desire: proc json out='./test.out' nosastags pretty; write open object; write values col1 abc; write values col2 cba; write close; run; Of course, a full solution would probably require some dynamic coding to properly update the file name (most likely with an incremented number appended to the base name) and including the proper data for each object from what I assume is a data set.

BillM_SAS · ‎11-13-2018

The JSON procedure writes the specified data to a file within a single JSON structure. Therefore you get as your output a JSON array containing 2 JSON objects. What you are asking for is to create a file containing 2 JSON objects. A single PROC JSON statement cannot produce what you desire. But note that the output you desire is not valid JSON. For one thing, the values assigned to “col1” and “col2” are strings and in JSON a string requires surrounding double quotes. Also, a JSON file contains a single JSON structure. When I put the contents of what you want in your JSON file into a JSON validator, like JSONLint, the validator says the file contains errors (even after making the column values proper JSON strings). Error: Parse error on line 4: ...", "col2": "bca"} { "col1": "ccc", " ---------------------^ Expecting 'EOF', '}', ',', ']', got '{'

BillM_SAS · ‎11-07-2018

Sorry. I forgot to mention you need SAS 9.4 maintenance 4 or better to have access to the JSON engine.

BillM_SAS · ‎11-06-2018

I put your example data line into the file json.txt and used it in the JSON LIBNAME statement. libname test json ".\sasuser\json.txt" map=".\sasuser\json.map" automap=reuse; run; proc print data=test.root(drop=ordinal_root); run; The results are what you desired: Obs seq acc Date 1 24425 1-1951 26-08-2018 2 24426 1-1952 27-08-2018 3 24427 1-1953 28-08-2018

BillM_SAS · ‎11-06-2018

The JSON procedure is output only. To read in that JSON looking data, try using the JSON LIBNAME statement, as others have suggested.

BillM_SAS · ‎11-01-2018

You will need to do a little extra processing to get the JSON output you desire. Instead of hardcoding the names and values from the 'sub_main' dataset, I used the SQL Dictionary tables to dynamically obtain the needed data. I then stored the data in macro variables and used those in the JSON procedure. This should give you an idea of a way to solve your problem. proc sql; select name into :colName1 - :colName3 from dictionary.columns where libname = 'WORK' and memname = 'SUB_MAIN' ; quit; %put &colName1, &colName2, &colName3; proc sql; select id, status, Rechtsform_ID into :colValue1, :colValue2, :colValue3 from WORK.SUB_MAIN ; quit; %put &colValue1, &colValue2, &colValue3; proc json out="output.json" pretty nosastags; write open object; write values &colName1 &colValue1; write values &colName2 &colValue2; write values &colName3 &colValue3; write values "Mitarbeiter"; write open array; export sub_ma; write close; write values "EFE"; write open array; export sub_efe; write close; write close; run;

BillM_SAS · ‎04-16-2018

There is an example in the documentation: HTTP Procedure Documentation Example 14: Using the Status Reporting Macro Variables

BillM_SAS · ‎04-16-2018

It works by excluding the number sign, "#", from the drug name: 73 proc http 74 url="https://rxnav.nlm.nih.gov/REST/approximateTerm.json?term=acetaminophen w/ codeine 3" 75 method= "GET" 76 out=med1; 77 run; NOTE: PROCEDURE HTTP used (Total process time): real time 0.31 seconds cpu time 0.01 seconds NOTE: 200 OK 78 %fileecho(med1); NOTE: The infile MED1 is: Filename=C:\Users\sasbim\AppData\Local\Temp\SAS Temporary Files\_TD15416_d10b742_\#LN00010, RECFM=V,LRECL=32767,File Size (bytes)=576, Last Modified=16Apr2018:13:39:38, Create Time=16Apr2018:13:18:32 {"approximateGroup":{"inputTerm":"acetaminophen w/ codeine 3","comment":"","candidate":[{"rxcui":" 817579","rxaui":"3297609","score":"50","rank":"1"},{"rxcui":"817579","rxaui":"798172","score":"50" ,"rank":"1"},{"rxcui":"817579","rxaui":"798171","score":"50","rank":"1"},{"rxcui":"817579","rxaui" :"3095428","score":"50","rank":"1"},{"rxcui":"817579","rxaui":"3095427","score":"50","rank":"1"},{ "rxcui":"817579","rxaui":"2626344","score":"50","rank":"1"},{"rxcui":"817579","rxaui":"3076140","s core":"50","rank":"1"},{"rxcui":"1945644","rxaui":"798175","score":"50","rank":"1"}]}} NOTE: 1 record was read from the infile MED1. The minimum record length was 576. The maximum record length was 576. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 79 libname resp1 JSON fileref=med1; NOTE: JSON data is only read once. To read the JSON again, reassign the JSON LIBNAME. NOTE: Libref RESP1 was successfully assigned as follows: Engine: JSON Physical Name: C:\Users\sasbim\AppData\Local\Temp\SAS Temporary Files\_TD15416_d10b742_\#LN00010 80 data Approximategroup_candidate; 81 set resp1.Approximategroup_candidate; 82 run; NOTE: There were 8 observations read from the data set RESP1.APPROXIMATEGROUP_CANDIDATE. NOTE: The data set WORK.APPROXIMATEGROUP_CANDIDATE has 8 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.03 seconds cpu time 0.00 seconds

BillM_SAS · ‎04-16-2018

By specifying TEMP for the file location in the filename statement, the output from running PROC HTTP is sent to a temporary file in the SAS temporary location. You could look at the temporary file by using this macro: %macro fileecho(fn); data _NULL_; infile &fn length=L; input @1 line $varying32767. L; put @1 line $varying32767. L; run; %mend fileecho; %fileecho(med1); Or, you could specify a file name in the FILENAME statement to write the HTTP output to a permanent file: FILENAME med1 './procHttpOutput.out'; But when I run the code you posted I see the following in the SAS Log window: NOTE: 400 Bad Request When I then look at the output from the PROC HTTP, I see an error message in XML format: <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"> <html><head> <title>400 Bad Request</title> </head><body> <h1>Bad Request</h1> <p>Your browser sent a request that this server could not understand.<br /> </p> <hr> <address>Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.2k-fips mod_perl/2.0.9 Perl/v5.22.1 S erver at mor.nlm.nih.gov Port 443</address> </body></html> The XML formatted message will definitely result in a illegal character message from the JSON engine. Since this works with other drugs, I suspect that there is a problem with how you are specifying the drug name. Did the drug names that worked include spaces in the drug name specified?

BillM_SAS · ‎04-16-2018

The message indicates that the JSON engine found what it considers an illegal JSON character in the file. Take a look at or post the file returned from running PROC HTTP.

BillM_SAS · ‎01-12-2018

As Chris notes, the JSON procedure can create a hierarchical JSON file. I recently posted proof-of-concept code showing how to use PROC JSON to create a hierarchical JSON file from a data set in the SASHELP library. You can see that post via this link.

BillM_SAS · ‎11-27-2017

As I noted when I posted the code, once I got into the details of the data, producing a solution was a bit more work than I anticipated. My assumption was that SAS programmers are more familiar with manipulating their data than dealing with the syntax of valid JSON output. Also, the original post expressed a desire to use the JSON procedure to solve the problem. The code I presented showed how that could be done.

BillM_SAS · ‎11-15-2017

Sorry for the delay. Once I got into the code and data, it was a bit more work than I anticipated. Plus I had some higher priority work to complete. I think I have code written using the SASHELP.shoes data set that is close to what you want. It at least should demonstrate a technique for producing hierarchical JSON formatted output with the JSON procedure. I am hoping that you can take the proof-of-concept code I've included and modify it to your specific requirements. As I note in the code comments, this is quick and dirty code to demonstrate the concept. I'm sure there are better ways to do what I did, but it should be a good start on the code you desire. /******************************************************************** * Proof-of-concept code to demonstrate a method of using the JSON * procedure available in SAS 9.4 to produce a hierarchical output file * in JSON format from a SAS data set. This code specifically takes * the Shoes data set from SASHLEP to create hierarchical output in * the form: * * Region -+ * | * +- Subsidiary -+ * | * +- Product -+ * | * +- <product data> * * Note that this code is not the most concise as some pieces are * hardcoded to allow for easier understanding of the parts and * resulting files. There are probably better ways to do some * sections of the code. This code could also be modified to make * it more easily handles other data sets. * * Modifications available in this code: * * dataFields = The comma-separated names of the data set * variables that make up the lowest level of * the hierarchy. * * h1 = The name of the data set variable that is the highest * level of the hierarchy. * * h2 = The name of the data set variable that is the second * level of the hierarchy. * * h3 = The name of the data set variable that is the third * level of the hierarchy. * * jsonOutputSpec = The location where the formatted JSON output * will be written after running the custom * JSON procedure code. * * jsonProcCodeSpec = The location where the custom JSON * procedure code is written. It is running * of this generated file that will produce * the JSON formatted output. * * sourceDataSet = The library and member name of the SAS data * set being written in JSON format. * * Author: Bill McNeill * Company: SAS Institute * Contact: Bill.McNeill@sas.com * Date: 14 November 2017 ********************************************************************/ /***************************** * Modifiable macro variables ******************************/ %let jsonProcCodeSpec=./sasuser/jsonProcCode.sas; %let jsonOutputSpec=./sasuser/jsonOutput.txt; %let sourceDataSet=SASHELP.Shoes; %let h1=region; %let h2=subsidiary; %let h3=product; %let dataFields=inventory, returns, stores, sales; /***************************** * Constant macro variables ******************************/ %let stmtEnd=%STR(;); %let byFieldsSQL=&h1, &h2, &h3; %let byFieldsSDS=&h1 &h2 &h3; /************************ ************************* * Macro functions * ************************* ************************/ /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: createLowLevelDataSet * Create the data set specific to each hierarchy. * Parameters: * count - The unique identification number for the data set. * value1 - The first level hierarchy value. * value2 - The second level hierarchy value. * value3 - The third level hierarchy value. * Results: * Create a data set named WORK.ds_# * where # is the unique identification number. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro createLowLevelDataSet(count, value1, value2, value3); proc sql; create table ds_&count as select &dataFields from &sourceDataSet where &h1=&value1 and &h2=&value2 and &h3=&value3 ; quit; %mend createLowLevelDataSet; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: fileHeader * Write the one-time JSON procedure statements at the start of * the JSON procedure. * Parameters: * filePath - The file specification to where the JSON * formatted output is written. * Results: * Output the first 2 lines of the JSON procedure to the file * containing the JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro fileHeader(filePath); put "proc json pretty out=""" &filePath """ nosastags &stmtEnd"; put "write open object &stmtEnd /* open outermost object */"; %mend fileHeader; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: hierarchy1Open * Write the JSON procedure statements to identify and open the * JSON containers at the level 1 hierarchy. * Parameters: * None * Results: * Write the needed JSON statements to the file containing the * JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro hierarchy1Open(); put "write value ""Name"" &stmtEnd"; valueStmt=CAT("write value """, TRIM(&h1), """ &stmtEnd"); put valueStmt; put "write value ""Children"" &stmtEnd"; put "write open array &stmtEnd /* open H1 array */"; put "write open object &stmtEnd /* open H1 object */ "; %mend hierarchy1Open; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: hierarchy1Close * Write the JSON procedure statements to close the JSON * containers to close the level 1 hierarchy. * Parameters: * None * Results: * Write the needed JSON close statements to the file * containing the JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro hierarchy1Close(); put "write close &stmtEnd /* close H1 object */"; put "write close &stmtEnd /* close H1 array */"; %mend hierarchy1Close; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: hierarchy2Open * Write the JSON procedure statements to identify and open the * JSON containers at the level 2 hierarchy. * Parameters: * None * Results: * Write the needed JSON statements to the file containing the * JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro hierarchy2Open(); put "write value ""Name"" &stmtEnd"; valueStmt=CAT("write value """, TRIM(&h2), """ &stmtEnd"); put valueStmt; put "write value ""Children"" &stmtEnd"; put "write open array &stmtEnd /* open H2 array */"; put "write open object &stmtEnd /* open H2 object */ "; %mend hierarchy2Open; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: hierarchy2Close * Write the JSON procedure statements to close the JSON * containers to close the level 2 hierarchy. * Parameters: * None * Results: * Write the JSON close statements to the file containing the * JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro hierarchy2Close(); put "write close &stmtEnd /* close H2 object */"; put "write close &stmtEnd /* close H2 array */"; %mend hierarchy2Close; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: hierarchy3Stmts * Write the JSON procedure statements to identify the JSON * containers that will hold the low-level hierarchy data. * Parameters: * None * Results: * Write the needed JSON statements to the file containing the * JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro hierarchy3Stmts(); put "write value ""Name"" &stmtEnd"; valueStmt=CAT("write value """, TRIM(&h3), """ &stmtEnd"); put valueStmt; put "write value ""Children"" &stmtEnd"; %mend hierarchy3Stmts; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Macro function: fileFooter * Write the one-time JSON procedure statements at the end of * the JSON procedure. * Parameters: * None * Results: * Output the last 2 lines of the JSON procedure to the file * containing the JSON procedure code. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ %macro fileFooter(); put "write close &stmtEnd /* close outermost object */"; put "run &stmtEnd"; %mend fileFooter; /************************ ************************* * Support Statements * ************************* ************************/ /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Create a data set, WORK.combos, containing the unique * combinations of the 3 variables that constitute the levels * of hierarchy. * * Note that this query produces a reduced data set in order to * keep the size of the formatted JSON output manageable for * review. To use the full data set, remove the WHERE statement * from the query. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ proc sql; create table work.combos as select distinct &byFieldsSQL from &sourceDataSet where (region = "Canada" or region = "Asia") /* just for reducing data */ ORDER BY &byFieldsSQL ; quit; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Create a sorted data set containing the unique combinations * of the 3 variables that constitute the levels of hierarchy. * This makes sure the hierarchies are complete. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ proc sort data=work.combos out=work.sortedShoes; by &byFieldsSDS; run; /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * Create the data sets containing the data in the lowest level * of the hierarchy. There is a data set for each unique * combination of hierarchy variables. These data sets are * needed because the DATA step WHERE statement requires the * variables in the WHERE also be in the data set results. * Using the DATA step WHERE statement within the JSON procedure * would result in duplicate information in the low-level * hierarchy data. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ data _null_; set work.sortedShoes; by &byFieldsSDS; call execute('%createLowLevelDataSet(' || _N_ || ',"' || TRIM(&h1) || '","' || TRIM(&h2) || '","' || TRIM(&h3) || '")'); run; /************************ ************************* * Main DATA Step * ************************* ************************/ /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ * The main DATA step to create the custom JSON procedure code * that will create the hierarchical JSON formatted output of * the Shoes data set. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/ data _null_; /* Process each observation in the WORK.sortedShoes data set. The END option set a flag when the last observation in the data set is read. The BY statement separates the data by the desired hierarchy. */ set work.sortedShoes end=lastOne; by &byFieldsSDS; /* specifies the output file for PUT statements */ FILE "&jsonProcCodeSpec" DISK; /* Only on the first observation in the data set, write the required initial statements to the JSON procedure code file. */ if _N_ eq 1 then do; %fileHeader("&jsonOutputSpec"); end; /* Each time the value of the level 1 hierarchy changes, write the needed open statements to the JSON procedure code file. */ if first.&h1 then do; %hierarchy1Open(); end; /* Each time the value of the level 2 hierarchy changes, write the needed open statements to the JSON procedure code file. */ if first.&h2 then do; %hierarchy2Open(); end; /* Each time the value of the level 3 hierarchy changes, write the needed identifying statements to the JSON procedure code file. */ if first.&h3 then do; %hierarchy3Stmts(); end; /* The lowest level of the hierarchy has been reached. write the open statement to the JSON procedure code file. Then, using the observation count, export the proper low-level data set, and then close the open JSON container. */ put "write open array &stmtEnd /* open array of data set objects */"; dsName=CAT("ds_",_N_); put "export " dsName "&stmtEnd"; put "write close &stmtEnd /* close array of data set objects */"; /* Each time the value of the level 2 hierarchy is about to changes, write the needed close statements to the JSON procedure code file.*/ if last.&h2 then do; %hierarchy2Close(); end; /* Each time the value of the level 1 hierarchy is about to changes, write the needed close statements to the JSON procedure code file.*/ if last.&h1 then do; %hierarchy1Close(); end; /* Only on the last observation in the data set, write to the JSON procedure code file the required statements to end the genearted JSON procedure. */ if lastOne then do; %fileFooter(); end; run; /* Now run the generated custom JSON procedure code to produce the JSON formatted output file of the data set. */ %include "&jsonProcCodeSpec";

BillM_SAS · ‎11-06-2017

Yes, it should be possible to create the output file format you reference. You did not supply or describe the form of the SAS data sets available, so I am unable to send you an brief example to demonstrate the ability specific to your posting. As Chris mentions and the video demonstrates, the JSON procedure has the ability to create hierarchical JSON files using the WRITE statements. The JSON procedure will make the generation of the file much easier than a DATA Step with PUT statements.

Online Status	Offline
Date Last Visited	‎11-08-2022 01:05 PM

Re: Need help to create a hierarchical JSON file using PROC JSON

Re: Need help to create a hierarchical JSON file using PROC JSON

Re: Need help to create a hierarchical JSON file using PROC JSON

Re: JSON Export with "type" and "value" requirements

Re: JSON Export with "type" and "value" requirements

Re: how to export Nested JSON with proc JSON?

Re: Extracting multiple values having same path in json using json map...

Re: Proc HTTP Method="Put" Running Correctly But Not Updating Product

Re: Parsing a JSON File

Re: How to wirte ALLDATA data set back to JSON

Re: how to export Nested JSON with proc JSON?

Re: Extracting multiple values having same path in json using json map...

Re: Proc HTTP Method="Put" Running Correctly But Not Updating Product

Re: How to wirte ALLDATA data set back to JSON

Re: JSON Export with the containers in order of the records

Re: proc JSON export

Re: Proc Json

Re: Proc Json

Re: How do I Read Multiple Delimited Single line file

Re: How do I Read Multiple Delimited Single line file

Re: How do I Read Multiple Delimited Single line file

Re: proc JSON export

Re: PROC HTTP error

Re: PROC HTTP error

Re: PROC HTTP error

Re: PROC HTTP error

Re: RTDM and JSON file integration

Re: PROC JSON - create a hierarchical file

Re: PROC JSON - create a hierarchical file

Re: PROC JSON - create a hierarchical file