topic Re: how to bring 3 lines into one line in SAS Enterprise Guide

how to bring 3 lines into one line

alepage — Tue, 09 May 2023 21:01:07 GMT

how to bring 3 lines like below

{ "firstName" :"Clint1", "lastName" :"Eastwood1", "email" :"Clint1.Eastwood1@hotmail.com", "extRef" :"235987",
"embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"15JUL2020:14:29:52",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint2", "lastName" :"Eastwood2", "email" :"Clint2.Eastwood2@hotmail.com", "extRef" :"236663",
"embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29AUG2022:08:25:57",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint3", "lastName" :"Eastwood3", "email" :"Clint3.Eastwood3@hotmail.com", "extRef" :"236900",
"embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29JUL2020:16:21:59",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },
{ "firstName" :"Clint4", "lastName" :"Eastwood4", "email" :"Clint4.Eastwood4@hotmail.com", "extRef" :"237009",
"embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"07NOV2019:04:24:10",
"Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" },

like that :

{ "firstName" :"Clint1", "lastName" :"Eastwood1", "email" :"Clint1.Eastwood1@hotmail.com", "extRef" :"235987","embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"15JUL2020:14:29:52","Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }
{ "firstName" :"Clint2", "lastName" :"Eastwood2", "email" :"Clint2.Eastwood2@hotmail.com", "extRef" :"236663","embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29AUG2022:08:25:57","Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }
{ "firstName" :"Clint3", "lastName" :"Eastwood3", "email" :"Clint3.Eastwood3@hotmail.com", "extRef" :"236900", "embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29JUL2020:16:21:59", "Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }
{ "firstName" :"Clint4", "lastName" :"Eastwood4", "email" :"Clint4.Eastwood4@hotmail.com", "extRef" :"237009","embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"07NOV2019:04:24:10","Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }

Re: how to bring 3 lines into one line

PaigeMiller — Tue, 09 May 2023 21:07:19 GMT

Is this a SAS question? It doesn't seem as if you are working with SAS data sets here.

Re: how to bring 3 lines into one line

alepage — Tue, 09 May 2023 21:10:52 GMT

It is a json file like a text file, and I would like to bring each row in one variable ex :
var1 into a sas dataset

Re: how to bring 3 lines into one line

Reeza — Tue, 09 May 2023 21:12:28 GMT

Like I mnetioned in your previous post it's a badly formed JSON file. How was it created? Ideally you fix it at source instead.

PROC JSON won't read the file directly unfortunately so otherwise you're parsing manually. Are the line returns in your dataset hard coded, or those just how it's viewed?

Re: how to bring 3 lines into one line

alepage — Tue, 09 May 2023 21:14:12 GMT

yes, there is carriage return line feed

Re: how to bring 3 lines into one line

alepage — Tue, 09 May 2023 21:16:25 GMT

the idea is to bring each record into a sas dataset under the variable jsoninfo. Then do a call execute on each record as you have proposed to do.

Re: how to bring 3 lines into one line

PaigeMiller — Tue, 09 May 2023 21:18:00 GMT

@alepage wrote:
It is a json file like a text file, and I would like to bring each row in one variable ex :
var1 into a sas dataset

We still need to see the equivalent SAS data set. We are asking you to provide (a portion of) the SAS data set.

Is your question that you don't know how to get it into SAS?

Re: how to bring 3 lines into one line

Reeza — Tue, 09 May 2023 22:14:58 GMT

I wouldn't bother reading it into a data set, you'll have to add back the JSON formatting later on anyways.

Instead, just delete the extra comma at the end of each series of information.

data have;
infile '/home/fkhurshed/CLASS2/example1.json';
length value $500.;
if mod(_n_, 3)=1 then record+1;
input;
value = _infile_;
run;

data clean;
set have;
by record;
if last.record then value = substr(value, 1, length(value)-1);
run;

Then write to each new JSON file dynamically.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/n15o12lpyoe4gfn1y1vcp6xs6966.htm#n0m1x5yncfgf4yn16d6ui4h6uinn

And instead of looping your old macro. Call it using CALL Execute after you write the last line of the output.

Probably needs some debugging - but I have to go take kiddo to swim class 🙂

%macro upload_contact(row= , filename=);
					filename respp&row. "%sysfunc(getoption(WORK))/Consentinfo&row..json";

filename dest&row "path to JSON file created in data step";
					PROC HTTP
						METHOD="POST"
    					URL= "&url_./directories/&&DirectoryId&j./contacts"
    					OUT=respp&row.
    					in=dest&j.;
    					headers
   						'x-api-token'= &Api_Token.
   						'Content-Type'='application/json';   
					run;
					
					libname respp&row. json "%sysfunc(getoption(WORK))/Consentinfo&row..json";
					proc datasets lib=respp&row.;
					run;

					data TrasactionContactsImportSummary&row.;
					set respp&row..alldata;
					run;

					data result&row.;
					set respp&row..result;
					call symputx("ContactId&row.",id,'g');
					run;
				%mend;

Re: how to bring 3 lines into one line

Tom — Wed, 10 May 2023 00:04:06 GMT

I cannot tell the difference between the two things. It does not help that you typed them into the body of your message instead of into a text box created by the Insert Code or Insert SAS Code editor icons.

Re: how to bring 3 lines into one line

Tom — Wed, 10 May 2023 00:15:56 GMT

If you know that you want to convert three lines into one then just do that.

data _null_;
  infile json truncover ;
  input line1 $200. / line2 $200. / line3 $200. ;
  file jsonl ;
  substr(line3,length(line3))=' ';
  put line1 line2 line3 ;
run;

Result

{ "firstName" :"Clint1", "lastName" :"Eastwood1", "email" :"Clint1.Eastwood1@hotmail.com", "extRef" :"235987", "embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"15JUL2020:14:29:52", "Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }
{ "firstName" :"Clint2", "lastName" :"Eastwood2", "email" :"Clint2.Eastwood2@hotmail.com", "extRef" :"236663", "embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29AUG2022:08:25:57", "Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }
{ "firstName" :"Clint3", "lastName" :"Eastwood3", "email" :"Clint3.Eastwood3@hotmail.com", "extRef" :"236900", "embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"29JUL2020:16:21:59", "Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }
{ "firstName" :"Clint4", "lastName" :"Eastwood4", "email" :"Clint4.Eastwood4@hotmail.com", "extRef" :"237009", "embeddedData" : { "Brand" :"Anthony Insurance", "Consent Date" :"07NOV2019:04:24:10", "Dataset Creation Timestamp" :"28MAR2023:22:01:07.758540" }, "unsubscribed" :"true" }

Or is your actual input file more complicated that three lines per "observation"?