based on the data structure .  Please refer to the two records provided  ... View more

I have replace the data Info_1; length info $5000 ; set oracle.?????????????_TMP (rename=(JSON_FORMAT=Info)); run; for proc sql; Create table Info_1 as select JSON_FORMAT as Info FROM bisasbo.qualtrix_34_jsonformat_TMP; quit; and everything works fine! Thanks all of you for your help ... View more

After reading all the remarks, I feel that I need to start over and explain what I would like to do and provide test datasets.   So the first step is to connect to an oracle db and read a table which is like a json file (ref: json_2.txt). Then this file is put into a dataset to see what SAS is reading.   data Info_1; length info $5000 ; set oracle.?????????????_TMP (rename=(JSON_FORMAT=Info)); run; the libname and part of the filename have bee replaced for safety purpose. Then this sas dataset is send into a json file (json_in_2.json).   data _null_; set Info_1; file dest1 LRECL=5000; put Info; run; I am not familiar with json file at all but when I look at the obtained result, some records are consecutive and other have a carriage return linefeed between.  I suppose that we should have a crlf between each records.  But maybe I am wrong.   The next step is to incorporate the value of the batchid into the datalines statements using the following code (ref: json_1.txt).   %let batchid=AAAAAAAAAAAAA2023; filename x2 "&path./output/json_in_1.json" mod; data _null_; file x2 ; input Info: $200. ; textresolved=dequote(resolve(quote(Info))); put textresolved; datalines; {"transactionMeta":{"batchId":"&batchid.","fields":["QueueName"]},"contacts":[ ; run; The script works fine but if I add a blank any where into the datalines statement, I will get a partial record.  Is there a way to solve that issue?  (Please feel free to correct that script if you dont like it)   Then the final goal is two find a nice way to append json_1.txt and json_2.txt to obtain a result similar to this file(ref : json_3.txt).     filename source ("&path./output/json_in_1.json","&path./output/json_in_2.json"); data all ; infile source LRECL=4000 ENCODING="LATIN1" TERMSTR=CRLF DLM='7F'x MISSOVER DSD ; length line $4000 ; INPUT line : $CHAR4000. ; run; I have used the sas import tool to see the kind of sas code generated and I have adapted it. But again, fell free to change it.   Please note that I have change the filename extension to permit to use the sas import tool. Also, the length of the real file will be pretty the same as for the test file.  I have increase the length to avoid warning and risk of truncanation but the first one is about 90 characters long while the second one is about 900 characters long. Could it be possible to explain me why I need LRRECL=4000 ?  How can we find the good value ?   filename source ("&path./output/json_in_1.json","&path./output/json_in_2.json"); data all ; infile source LRECL=4000 ENCODING="LATIN1" TERMSTR=CRLF DLM='7F'x MISSOVER DSD ; length line $4000 ; INPUT line : $CHAR4000. ; run;                 ... View more

That what I though.  A json file is just like a text file.   I have tried this code: %let batchid=AAAAAAAAAAAAA2023; filename x2 "&path./output/json_in_4.json" mod; data _null_; file x2 ; input Info: $200. ; textresolved=dequote(resolve(quote(Info))); put textresolved; datalines; {"transactionMeta":{"batchId":"&batchid.","fields":["QueueName"]},"contacts":[ ; run; options mprint mlogic symbolgen; filename in ("&path./output/json_in_4.json"); data all ; infile in lrecl=4000; length line $4000 ; line=_infile_; run; NOTE: The file X2 is: Filename=/.....json_in_4.json, Owner Name=*****, Group Name=*****, Access Permission=-rwxrwxrwx, Last Modified=01 mars 2023 22 h 25, File Size (bytes)=174 SYMBOLGEN: Macro variable BATCHID resolves to AAAAAAAAAAAAA2023 NOTE: 1 record was written to the file X2. The minimum record length was 86. The maximum record length was 86. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds   *********************************   data all ; 44 infile in lrecl=4000; 45 length line $4000 ; 46 line=_infile_; 47 run; NOTE: The infile IN is: Filename=/.../json_in_4.json, Owner Name=********,Group Name=*********, Access Permission=-rwxrwxrwx, Last Modified=01 mars 2023 22 h 28, 3 The SAS System 22:25 Wednesday, March 1, 2023 File Size (bytes)=261 NOTE: 0 records were read from the infile IN. NOTE: The data set WORK.ALL has 1 observations and 1 variables. NOTE: Compressing data set WORK.ALL increased size by 100.00 percent. Compressed is 2 pages; un-compressed would require 1 pages. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds It is like SAS EG is not able to read the json file ... View more

we can test by appending twice the file json_in_4.json   The contents of  json_in_4.json is now: {"transactionMeta":{"batchId":"AAAAAAAAAAAAA2023","fields":["QueueName"]},"contacts":[   ... View more

I am still not able to read the json file as a text file. NOTE: 0 records were read from the infile IN.   filename in ("&path./output/json_in_4.json","&path./output/json_in_3.json"); data all ; infile in dsd truncover; length line $4000 ; line=_infile_; run; ... View more

I have found how to put the macro variable value into the datalines statements. see the sas code below.   %let batchid=AAAAAAAAAAAAA2023; filename x2 "&path./output/json_in_4.json" mod; data _null_; file x2 ; input Info: $200. ; textresolved=dequote(resolve(quote(Info))); put textresolved; datalines; {"transactionMeta":{"batchId":"&batchid.","fields":["QueueName"]},"contacts":[ ; run; The contents of  json_in_4.json is now: {"transactionMeta":{"batchId":"AAAAAAAAAAAAA2023","fields":["QueueName"]},"contacts":[   ... View more

Hello Kurt, Thank for the information. it is pretty close but I am loosing the Z after the second 2018-09-15T14:52:22+00:00 ... View more

Hello,    I have saw your example at the url: https://communities.sas.com/t5/SAS-Programming/How-to-make-a-PROC-HTTP-Post-request-by-passing-a-JSON-message/td-p/476946   where you are using the following code which I think is easier to read. Does the datalines portion will look like that and with or without the apostrophe?  Do we put a carriage return line feed to separate each new contact info ?   datalines; { "transactionMeta": { "batchId": "BT_ZZZZ45678901234", "fields": [ "string" ] }, "contacts": [ { "firstName": "string", "lastName": "string", "email": "string", "extRef": "string", "embeddedData": { "property1": "string", "property2": "string" }, "language": "string", "unsubscribed": true, "transactionData": {} } ] } run; /* Put the body of the JSON content in external file */ filename json_in temp; data _null_; file json_in; input; put _infile_; datalines; { "name":"value" } run;   ... View more

Thank you very for showing me how to use that function ... View more