BookmarkSubscribeRSS Feed
mitchum
Fluorite | Level 6

Hi,

I'm pretty new to SAS and not sure if we can achieve it, but i try to generate a json form a table.

Let say I get this dataset :

 

data testdataset;
  input sku$ label$ search__XX$ search__YY$ enabled;
datalines;
123-456 productA  producAX  productAY 1
123-789 productB  producBX  productBY 0
456-789 productC  producCX  productCY 1
;

 


I want to transform it to

 

{"identifier":"123-456","values":{"label":[{"scope":null,"data":"productA"}],"search":[{"scope":"XX","data":"producAX"},{"scope":"YY","data":"productAY"}],"enabled":[{"scope":null,"data":true}]}}
{"identifier":"123-789","values":{"label":[{"scope":null,"data":"productB"}],"search":[{"scope":"XX","data":"producBX"},{"scope":"YY","data":"productBY"}],"enabled":[{"scope":null,"data":false}]}}
{"identifier":"456-789","values":{"label":[{"scope":null,"data":"productC"}],"search":[{"scope":"XX","data":"producCX"},{"scope":"YY","data":"productCY"}],"enabled":[{"scope":null,"data":true}]}}

I try do understand the doc of proc json but when i try something like

proc json out="&ftpit\jsontest.json" pretty nosastags; 
     export testdataset; 
run;


I got extra array [ ] on my json, and also a comma between each items and i'm struggle to "split" my columns inside the "value" tag, but also be able to put specific column inside the right value

Literraly i think i need to do something like a loop for each row of my dataset and 

-create json
- create key idendifier and put testdataset.sku

-create key values
- create key label and put [{"scope":null,"data":testdataset.label}]
- put coma
- create key search and put [{"scope":"XX","data":testdataset.search__XX},{"scope":"YY","data":testdataset.search__YY}]
- put coma
- create key "enabled" and put [{"scope":null,"data": case when testdataset.enabled = 1 then true else false }]
- close json

not sure if it's something doable via sas ?

Thanks for your help

4 REPLIES 4
Ksharp
Super User
data testdataset;
  input sku$ label$ search__XX$ search__YY$ enabled;
datalines;
123-456 productA  producAX  productAY 1
123-789 productB  producBX  productBY 0
456-789 productC  producCX  productCY 1
;
data _null_;
 set testdataset;
 file "c:\temp\want.json";
_enabled=ifc(enabled=1,'true ','false');
put '{"identifier":"' sku +(-1)'","values":{"label":[{"scope":null,"data":"' 
    label +(-1)'"}],"search":[{"scope":"XX","data":"' 
    search__XX  +(-1)'"},{"scope":"YY","data":"'  
    search__YY  +(-1)'"}],"enabled":[{"scope":null,"data":'
    _enabled +(-1)'}]}}' ;
run;
Tom
Super User Tom
Super User

That is not a JSON file.  Perhaps you meant that you wanted to create a JSONL file?

 

mitchum
Fluorite | Level 6

Hi,

I finally get something working by doing without the proc json but doing that :


data testdataset;
  input sku$ label$ search__XX$ search__YY$ enabled;
datalines;
123-456 productA  producAX  productAY 1
123-789 productB  producBX  productBY 0
456-789 productC  producCX  productCY 1
;

 

data _null_;
  set temp;
  filename payload temp;
  /* Create an array to store key-value pairs */
  array json_arr[100] $1000 _temporary_;

  /* Generate the JSON payload for each observation */
  length json_in_str $32767; /* Adjust the length as needed */
  retain json_in_str;
  file payload; /* Create a temporary file for the JSON payload */
  /*file print; */
  *filename payload clear;
  
  /* Start building the JSON payload for each observation */
  json_in_str = '{"identifier":"' || compress(sku) || '","enabled":' || put(enabled, 1. -l) || ',"values":{';
  
  /* Add the key-value pairs to the array */
  /*json_arr[1] = '';*/
  json_arr[3] = ' "availibility_code":[{"scope":null,"locale":null,"data":"' || availibility_code || '"}],';
  json_arr[6] = ' "ean":[{"scope":null,"locale":null,"data":"' || compress(ean) || '"}],';
  json_arr[17] = ' "label":[{"scope":"XX","locale":null,"data":"' || compress(label__XX) || '"},{"scope":"YY","locale":null,"data":"' || compress(label__YY) || '"}],';
  
.... /* Combine all the key-value pairs from the array */ do i = 1 to dim(json_arr); if not missing(json_arr[i]) then json_in_str = catx(' ', json_in_str, json_arr[i]); end; /* Finish building the JSON payload */ json_in_str = catx(' ', json_in_str, '}}'); put json_in_str; /* Output the JSON payload for the current observation */ run;


So basically i end up with the expected json format.

Thanks 

Tom
Super User Tom
Super User

If you are going to write a text file anyway you can let the PUT statement to most of the work.  So no need to construct the line in a character variable and then write.  Just write it like you would when writing out any other text file.

filename payload temp;

data _null_;
  set temp;
  file payload;  
  put '{"identifier":' sku :$quote. ',"enabled":' enabled ',"values":{' @;
  
etc.

Plus that means the lines in your JSON LINES file are not limited to 32,767 bytes of a single dataset variable.   The LRECL= option of the FILE statement will support much longer lines.  Or you could use RECFM=N and write the end of line characters yourself using PUT '0DOA'x ;

 

 

NOTE: You are still not creating an actual JSON file.  Instead each line in your file is an complete JSON file.  So that is a JSON LINES file. For the whole file to be valid JSON you would need to combine the lines into a JSON object or array.

 

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 1032 views
  • 6 likes
  • 3 in conversation