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" }
Is this a SAS question? It doesn't seem as if you are working with SAS data sets here.
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.
@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?
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.
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;
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.
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"?
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 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.