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
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;
That is not a JSON file. Perhaps you meant that you wanted to create a JSONL file?
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: