topic Re: how to append two text files in SAS Enterprise Guide

how to append two text files

alepage — Wed, 01 Mar 2023 20:26:16 GMT

Hello,

I am reading an Oracle table which contains a json file. Then I would like to create another json file using datalines (see code). Thereafter append both to get the wanted file.

I have issue with json_in_4.json.

First, if there is a space in the datalines information, it reads only the first term instead of reading the full line.

I would like to convert/replace the value of batchid by is value and that into the datalines statements.

and to always replace the content of json_in_4.json.

data Info_1;
length info $4000 ;
set bi.**********jsonformat_TMP (rename=(JSON_FORMAT=Info));
run;

filename x1 "&path./output/json_in_3.json";
data _null_;
     set Info_1;
     file x LRECL=300;
put Info;
run;

%let batchid=AAAAAAAAAAAAA2023;
filename x2 "&path./output/json_in_4.json" mod;
data _null_;
	file x2 ;
  	input Info: $200. ;
  	put Info;
datalines;
{"transactionMeta":  {"batchId":"&batchid.","fields":["QueueName"]},"contacts":[
;
run;

Re: how to append two text files

alepage — Wed, 01 Mar 2023 20:52:42 GMT

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":[

Re: how to append two text files

Patrick — Wed, 01 Mar 2023 21:25:57 GMT

Alternatively you could also look at the macro variable name as a token that you replace using tranwrd()

%let batchid=AAAAAAAAAAAAA2023;
/*filename x2 "&path./output/json_in_4.json" mod;*/
data _null_;
/*	file x2 ;*/
  file print;
  input Info: $200. ;
  length textresolved txtresolved2 $200;
  textresolved=dequote(resolve(quote(Info)));
  txtresolved2=tranwrd(info,'&batchid.',"&batchid");
	put textresolved=;
	put txtresolved2=;
datalines;
{"transactionMeta":{"batchId":"&batchid.","fields":["QueueName"]},"contacts":[
;
run;

Re: how to append two text files

alepage — Wed, 01 Mar 2023 21:39:39 GMT

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;

Re: how to append two text files

Patrick — Wed, 01 Mar 2023 21:57:06 GMT

Can you share your json file (attach to the discussion) so we have something to test with. ...or some mock-up that's good enough for testing will do as well.

Re: how to append two text files

alepage — Wed, 01 Mar 2023 23:07:44 GMT

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":[

Re: how to append two text files

Patrick — Thu, 02 Mar 2023 00:44:49 GMT

A json is just a text file. What's not working? Please share the actual code and SAS log. Or share a mock-up json as attachment representative for your real file.

Re: how to append two text files

alepage — Thu, 02 Mar 2023 03:32:55 GMT

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

Re: how to append two text files

Patrick — Thu, 02 Mar 2023 04:00:55 GMT

There is no INPUT statement in your code.

Re: how to append two text files

Tom — Thu, 02 Mar 2023 04:16:37 GMT

I don't see two text files here.

If you have an existing text file and you want copy another text file to the end of it a simple data step should work:

data _null_;
  infile 'any other file name';
  file 'some file name' mod;
  input;
  put _infile_;
run;

If you have a dataset and you want to append the data in it to an existing file then you just need a data step.

If the dataset is named JSON_DATA and it has a variable named JSON_TEXT that you want to write then the code would look like:

data _null_;
  set json_data;
  file 'some file name' mod;
  put json_text ;
run;

Re: how to append two text files

Kurt_Bremser — Thu, 02 Mar 2023 08:04:07 GMT

Without an INPUT statement, nothing is read.

@alepage wrote:

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;

Re: how to append two text files

alepage — Thu, 02 Mar 2023 14:36:58 GMT

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;

Re: how to append two text files

alepage — Thu, 02 Mar 2023 15:06:43 GMT

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

Re: how to append two text files

Tom — Thu, 02 Mar 2023 15:41:32 GMT

Two things stand out to me in this that really do not make any sense.

First: Why are you using DATALINES to write a text file? Why not just use PUT directly? Is your real problem more complex?

%let batchid=AAAAAAAAAAAAA2023;
filename x2 "&path./output/json_in_1.json" mod;
data _null_;
  file x2 ;
  put "{""transactionMeta"":{""batchId"":""&batchid."",""fields"":[""QueueName""]},""contacts"":[" 
  ;
run;

Also tat looks like the BEGINNING of JSON and not something to add at the END of an existing JSON file. Where is the closing ] and closing }?

Second: Why are you at the end trying to read the JSON file as text?

If you wanted to do that why not just use the original dataset that already had the text in a variable?