topic Re: transform dataset to a specific json format with condition in Developers

transform dataset to a specific json format with condition

mitchum — Thu, 03 Aug 2023 14:57:05 GMT

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

Re: transform dataset to a specific json format with condition

Ksharp — Fri, 04 Aug 2023 17:47:58 GMT

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;

Re: transform dataset to a specific json format with condition

Tom — Fri, 04 Aug 2023 14:06:51 GMT

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

Re: transform dataset to a specific json format with condition

mitchum — Sun, 06 Aug 2023 07:54:12 GMT

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

Re: transform dataset to a specific json format with condition

Tom — Sun, 06 Aug 2023 13:48:16 GMT

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.