how to create automatically the json file header with sas

[alepage]

Hello,

I would like to find a way to automatically create the json file header below:

{"transactionMeta":{"batchId":"BT_10VriPO92D6e9NF", "fields":[ "distributor", "transactionDate", "transactionType", "customerTenure", "policyNumber", "policyType", "cntrct_ref_nbr", "dim_pl_cntrct_ver_key", "productHolding", "agentId", "agentEmployeeNbr", "agentName", "agentEmail", "agentManager", "managerEmail", "agentTeamCd", "agentHireDt" ]},"contacts":[

The "batchId":"BT_10VriPO92D6e9NF" is put into a macro variable using a proc http call followed by call symputx('BatchId',id,'g') .  The "contacts":[ could be also put into a macro variable.

It remains the fields portion: 

"fields":[ "distributor", "transactionDate", "transactionType", "customerTenure", "policyNumber", "policyType", "cntrct_ref_nbr", "dim_pl_cntrct_ver_key", "productHolding", "agentId", "agentEmployeeNbr", "agentName", "agentEmail", "agentManager", "managerEmail", "agentTeamCd", "agentHireDt" ]

So is there a way to automatically generate the header based on the sample data below:

{"firstName":"Gustave1","lastName":"Karma1","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000001","policyType":"AUP","cntrct_ref_nbr":11111101,"dim_pl_cntrct_ver_key":10000001,"productHolding":"AUP","agentId":"RRRRRR01","agentEmployeeNbr":"777701","agentName":"mr1 unknow1","agentEmail":"someone@hotmail.com","agentManager":"Mg1 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA1","agentHireDt":"2021-10-18"}},

{"firstName":"Gustave10","lastName":"Karma10","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"FORMER UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-18","transactionType":"AUTOMOD","customerTenure":11,"policyNumber":"10000010","policyType":"AUP","cntrct_ref_nbr":11111110,"dim_pl_cntrct_ver_key":10000010,"productHolding":"AUP","agentId":"RRRRRR10","agentEmployeeNbr":"777710","agentName":"mr10 unknow10","agentEmail":"someone@hotmail.com","agentManager":"Mg10 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA10","agentHireDt":"2022-06-13"}}]}

[ballardw]

Automatically? From what?

[alepage]

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

[ballardw]

---

@alepage wrote:

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

---

Don't see any "records". I see a lot of text. They aren't records until actually in a JSOn file as I understand it until in a properly structured  complete file.

Also you need to be aware that text posted into the main message windows on this forum gets reformatted. So what we see is very likely not actually what is your text. You want to post text like that in a text box opened on the forum with the </> icon above the main message window. Otherwise there have been Html tags inserted and/or multiple spaces compressed or replaced with tabs.

[Tom]

How are you creating the JSON file now?

[alepage]

I read the json file from an oracle table

[alepage]

and I need to generate the fields information related to the transactionsdata section

[Tom]

---

@alepage wrote:
and I need to generate the fields information related to the transactionsdata section

---

That does not really help.

1. Are you creating a brand new JSON file? Or modifying an existing JSON file?
2. What is the source of the DATA you want to put into the JSON file?
3. What is the source of the "fields information"? 
4. What does "fields information" actually mean here? Are you talking about the names of the variables your want to write into the JSON file?

[alepage]

As I have mentionned previously, I am reading a json file from an oracle table.

the original json file look like that (show only the two las records)

9 {"firstName":"Gustave9","lastName":"Karma9","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000009","policyType":"AUP","cntrct_ref_nbr":11111109,"dim_pl_cntrct_ver_key":10000009,"productHolding":"AUP","agentId":"RRRRRR09","agentEmployeeNbr":"777709","agentName":"mr9 unknow9","agentEmail":"someone@hotmail.com","agentManager":"Mg9 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA9","agentHireDt":"2021-04-12"}},

10 {"firstName":"Gustave10","lastName":"Karma10","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"FORMER UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-18","transactionType":"AUTOMOD","customerTenure":11,"policyNumber":"10000010","policyType":"AUP","cntrct_ref_nbr":11111110,"dim_pl_cntrct_ver_key":10000010,"productHolding":"AUP","agentId":"RRRRRR10","agentEmployeeNbr":"777710","agentName":"mr10 unknow10","agentEmail":"someone@hotmail.com","agentManager":"Mg10 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA10","agentHireDt":"2022-06-13"}}]}

If you look at the transactionsData, you see distributor, transactionDate, TransactionType,CustomerTenure, policyNumber,policyType,cntrct_ref_nbr,dim_plcntrct_ver_key, productHolding,AgentId,AgentEmployeeNbr, agentName, AgentEmail, agentManager, ManagerEmail,agentTeamcd and agenthiredt.

So I am looking an automatic way to pull the above field name from the original json file in order to generate this header automatically

{"transactionMeta":{"batchId":"BT_10VriPO92D6e9NF", "fields":[ "distributor", "transactionDate", "transactionType", "customerTenure", "policyNumber", "policyType", "cntrct_ref_nbr", "dim_pl_cntrct_ver_key", "productHolding", "agentId", "agentEmployeeNbr", "agentName", "agentEmail", "agentManager", "managerEmail", "agentTeamCd", "agentHireDt" ]},"contacts":[

Then this header is added to the original json file and will be uploaded using a proc Http procedure.

So if we go back to this header, {"transactionMeta":{"batchId":"BT_10VriPO92D6e9NF", the batchid is obtained via a proc http call and is put in a macro variable. Then come the fields portion: 

"fields":[ "distributor", "transactionDate", "transactionType", "customerTenure", "policyNumber", "policyType", "cntrct_ref_nbr", "dim_pl_cntrct_ver_key", "productHolding", "agentId", "agentEmployeeNbr", "agentName", "agentEmail", "agentManager", "managerEmail", "agentTeamCd", "agentHireDt" ]}, followed by "contacts":[

Is there a way to generate this header automatically and put its content into a macro variable to add this one into the original json file and save it under another name.

[Tom]

You still keep skipping a lot of details here.

So assuming you now have a SAS dataset named JSON with two variables ROWNUM and TEXT you can use that to create an actual JSON file by writing it out.  Make sure to write the lines in the right order or else the JSON file will be gibberish.

filename json temp;
data _null_;
  set json;
  by rownum;
  put text ;
run;

You can then use the JSON libref engine to see if SAS can parse that JSON text into variables.

libname json json ;
proc copy inlib=json outlib=work;
run;

Now you might be able to query one or more of the datasets that SAS created from the JSON file to get the list of "fields".

Otherwise you might be able to use SCAN() function and/or regular expressions to extract field names from the TEXT string you have.

[Tom]

---

@alepage wrote:

I read the json file from an oracle table

---

So show the details of the fields you are reading from the Oracle table.   Are you reading a single character string from the Oracle database whose content has JSON text?   Does it have a whole JSON file in a single value?  Or is the JSON text split into multiple JSON strings, sort like a JSONL file would have?

Or are you saying that the fields you want to write to the JSON file are actual variables in the Oracle table and you need generate JSON text from those fields?  

What is it you need to create? Do you need to write an actual JSON text file?  Do you need to upload something back into Oracle?

[alepage]

data have (drop= group:);
infile '/myfolder/have_2.json' recfm=n dsd dlm='[]{},:';
input temp : $4000. @@;
if missing(temp) then group+1;
if findw(temp,'headers') or
find(temp,'"rows"') or
prxmatch('/^\s/',temp) then delete;
if find(temp,"transactionData",'i') ge 1 then group2 + 1;
if find(temp,"transactionData",'i') ge 1 and group2=1 then call symputx('group',group + 1,'g');
if group = &group. and mod(_n_,2) <> 0 then output;
run;
proc sql;
create table have1 as
select monotonic() as rownum,*
from have;
quit;
proc sql;
select distinct (quote(strip(temp))), rownum
into :fields separated by ',',:rownum
from have1
order by rownum;
quit;
%put &=fields;
%let batchid=BT_10VriPO92D6e9NF;
%let header1={"transactionMeta":{"batchId":"&Batchid.", "fields":[;
%let header2=]},"contacts":[;
%let jsonheader=&header1&fields&header2.;
%put &=jsonheader;

[Tom]

I am totally confused now.  That code is reading in a TEXT file.  What happened to the Oracle database records?

[alepage]

filename myjson temp;
data _null_;
 file myjson;
 infile datalines;
 input;
 put _infile_;
datalines;
{"firstName":"Gustave1","lastName":"Karma1","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000001","policyType":"AUP","cntrct_ref_nbr":11111101,"dim_pl_cntrct_ver_key":10000001,"productHolding":"AUP","agentId":"RRRRRR01","agentEmployeeNbr":"777701","agentName":"mr1 unknow1","agentEmail":"someone@hotmail.com","agentManager":"Mg1 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA1","agentHireDt":"2021-10-18"}},
{"firstName":"Gustave2","lastName":"Karma2","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"ACTIVE","postalCode":"H0H 0H0","provinceCode":"ON"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-18","transactionType":"HOMEMOD","customerTenure":12,"policyNumber":"10000002","policyType":"RE","cntrct_ref_nbr":11111102,"dim_pl_cntrct_ver_key":10000002,"productHolding":"ALL","agentId":"RRRRRR02","agentEmployeeNbr":"777702","agentName":"mr2 unknow2","agentEmail":"someone@hotmail.com","agentManager":"Mg2 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA2","agentHireDt":"2021-10-22"}},
{"firstName":"Gustave3","lastName":"Karma3","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"Y","ubiStatus":"REGISTERED_APP","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-09","transactionType":"NEWHOME","customerTenure":0,"policyNumber":"10000003","policyType":"RE","cntrct_ref_nbr":11111103,"dim_pl_cntrct_ver_key":10000003,"productHolding":"ALL","agentId":"RRRRRR03","agentEmployeeNbr":"777703","agentName":"mr3 unknow3","agentEmail":"someone@hotmail.com","agentManager":"Mg3 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA3","agentHireDt":"2022-09-26"}},
{"firstName":"Gustave4","lastName":"Karma4","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000004","policyType":"AUP","cntrct_ref_nbr":11111104,"dim_pl_cntrct_ver_key":10000004,"productHolding":"AUP","agentId":"RRRRRR04","agentEmployeeNbr":"777704","agentName":"mr4 unknow4","agentEmail":"someone@hotmail.com","agentManager":"Mg4 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAA4","agentHireDt":"2022-06-13"}},
{"firstName":"Gustave5","lastName":"Marma5","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"ENROLLED PROGRAM","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-12","transactionType":"AUTOMOD","customerTenure":1,"policyNumber":"10000005","policyType":"AUP","cntrct_ref_nbr":11111105,"dim_pl_cntrct_ver_key":10000005,"productHolding":"AUP","agentId":"RRRRRR05","agentEmployeeNbr":"777705","agentName":"mr5 unknow5","agentEmail":"someone@hotmail.com","agentManager":"Unknown","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA5","agentHireDt":"2022-09-26"}},
{"firstName":"Gustave6","lastName":"Karma6","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"ON"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"AUTOMOD","customerTenure":123,"policyNumber":"10000006","policyType":"AUP","cntrct_ref_nbr":11111106,"dim_pl_cntrct_ver_key":10000006,"productHolding":"ALL","agentId":"RRRRRR06","agentEmployeeNbr":"777706","agentName":"mr6 unknow6","agentEmail":"someone@hotmail.com","agentManager":"Mg6 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA6","agentHireDt":"2022-05-27"}},
{"firstName":"Gustave7","lastName":"Karma7","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"NEWDUO","customerTenure":0,"policyNumber":"10000007","policyType":"RE","cntrct_ref_nbr":11111107,"dim_pl_cntrct_ver_key":10000007,"productHolding":"ALL","agentId":"RRRRRR07","agentEmployeeNbr":"777707","agentName":"mr7 unknow7","agentEmail":"someone@hotmail.com","agentManager":"Mg7 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA7","agentHireDt":"2019-01-14"}},
{"firstName":"Gustave8","lastName":"Karma8","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"AB"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-20","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000008","policyType":"AUP","cntrct_ref_nbr":11111108,"dim_pl_cntrct_ver_key":10000008,"productHolding":"AUP","agentId":"RRRRRR08","agentEmployeeNbr":"777708","agentName":"mr8 unknow8","agentEmail":"someone@hotmail.com","agentManager":"Unknown","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA8","agentHireDt":"2020-03-02"}},
{"firstName":"Gustave9","lastName":"Karma9","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000009","policyType":"AUP","cntrct_ref_nbr":11111109,"dim_pl_cntrct_ver_key":10000009,"productHolding":"AUP","agentId":"RRRRRR09","agentEmployeeNbr":"777709","agentName":"mr9 unknow9","agentEmail":"someone@hotmail.com","agentManager":"Mg9 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA9","agentHireDt":"2021-04-12"}},
{"firstName":"Gustave10","lastName":"Karma10","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"FORMER UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-18","transactionType":"AUTOMOD","customerTenure":11,"policyNumber":"10000010","policyType":"AUP","cntrct_ref_nbr":11111110,"dim_pl_cntrct_ver_key":10000010,"productHolding":"AUP","agentId":"RRRRRR10","agentEmployeeNbr":"777710","agentName":"mr10 unknow10","agentEmail":"someone@hotmail.com","agentManager":"Mg10 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA10","agentHireDt":"2022-06-13"}}
;
run;

libname dest1 JSON fileref=myjson;
proc copy inlib=dest1 outlib=work;
run;

What we read from the oracle table is what you see into the datalines and it seems that it does not like the comma at the first line, column 705.

[Tom · Accepted solution]

To turn those lines into a JSON file you need add a container for those objects.

filename mylines temp;
data _null_;
 file mylines;
 infile datalines;
 input;
 put _infile_;
datalines4;
{"firstName":"Gustave1","lastName":"Karma1","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000001","policyType":"AUP","cntrct_ref_nbr":11111101,"dim_pl_cntrct_ver_key":10000001,"productHolding":"AUP","agentId":"RRRRRR01","agentEmployeeNbr":"777701","agentName":"mr1 unknow1","agentEmail":"someone@hotmail.com","agentManager":"Mg1 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA1","agentHireDt":"2021-10-18"}},
{"firstName":"Gustave2","lastName":"Karma2","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"ACTIVE","postalCode":"H0H 0H0","provinceCode":"ON"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-18","transactionType":"HOMEMOD","customerTenure":12,"policyNumber":"10000002","policyType":"RE","cntrct_ref_nbr":11111102,"dim_pl_cntrct_ver_key":10000002,"productHolding":"ALL","agentId":"RRRRRR02","agentEmployeeNbr":"777702","agentName":"mr2 unknow2","agentEmail":"someone@hotmail.com","agentManager":"Mg2 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA2","agentHireDt":"2021-10-22"}},
{"firstName":"Gustave3","lastName":"Karma3","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"Y","ubiStatus":"REGISTERED_APP","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-09","transactionType":"NEWHOME","customerTenure":0,"policyNumber":"10000003","policyType":"RE","cntrct_ref_nbr":11111103,"dim_pl_cntrct_ver_key":10000003,"productHolding":"ALL","agentId":"RRRRRR03","agentEmployeeNbr":"777703","agentName":"mr3 unknow3","agentEmail":"someone@hotmail.com","agentManager":"Mg3 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA3","agentHireDt":"2022-09-26"}},
{"firstName":"Gustave4","lastName":"Karma4","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"FR","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000004","policyType":"AUP","cntrct_ref_nbr":11111104,"dim_pl_cntrct_ver_key":10000004,"productHolding":"AUP","agentId":"RRRRRR04","agentEmployeeNbr":"777704","agentName":"mr4 unknow4","agentEmail":"someone@hotmail.com","agentManager":"Mg4 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAA4","agentHireDt":"2022-06-13"}},
{"firstName":"Gustave5","lastName":"Marma5","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"ENROLLED PROGRAM","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-12","transactionType":"AUTOMOD","customerTenure":1,"policyNumber":"10000005","policyType":"AUP","cntrct_ref_nbr":11111105,"dim_pl_cntrct_ver_key":10000005,"productHolding":"AUP","agentId":"RRRRRR05","agentEmployeeNbr":"777705","agentName":"mr5 unknow5","agentEmail":"someone@hotmail.com","agentManager":"Unknown","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA5","agentHireDt":"2022-09-26"}},
{"firstName":"Gustave6","lastName":"Karma6","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"ON"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"AUTOMOD","customerTenure":123,"policyNumber":"10000006","policyType":"AUP","cntrct_ref_nbr":11111106,"dim_pl_cntrct_ver_key":10000006,"productHolding":"ALL","agentId":"RRRRRR06","agentEmployeeNbr":"777706","agentName":"mr6 unknow6","agentEmail":"someone@hotmail.com","agentManager":"Mg6 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA6","agentHireDt":"2022-05-27"}},
{"firstName":"Gustave7","lastName":"Karma7","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"NEWDUO","customerTenure":0,"policyNumber":"10000007","policyType":"RE","cntrct_ref_nbr":11111107,"dim_pl_cntrct_ver_key":10000007,"productHolding":"ALL","agentId":"RRRRRR07","agentEmployeeNbr":"777707","agentName":"mr7 unknow7","agentEmail":"someone@hotmail.com","agentManager":"Mg7 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA7","agentHireDt":"2019-01-14"}},
{"firstName":"Gustave8","lastName":"Karma8","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"AB"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-20","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000008","policyType":"AUP","cntrct_ref_nbr":11111108,"dim_pl_cntrct_ver_key":10000008,"productHolding":"AUP","agentId":"RRRRRR08","agentEmployeeNbr":"777708","agentName":"mr8 unknow8","agentEmail":"someone@hotmail.com","agentManager":"Unknown","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA8","agentHireDt":"2020-03-02"}},
{"firstName":"Gustave9","lastName":"Karma9","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"N","ubiStatus":"NO UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-19","transactionType":"QUOAUTO","customerTenure":0,"policyNumber":"10000009","policyType":"AUP","cntrct_ref_nbr":11111109,"dim_pl_cntrct_ver_key":10000009,"productHolding":"AUP","agentId":"RRRRRR09","agentEmployeeNbr":"777709","agentName":"mr9 unknow9","agentEmail":"someone@hotmail.com","agentManager":"Mg9 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA9","agentHireDt":"2021-04-12"}},
{"firstName":"Gustave10","lastName":"Karma10","email":"someone@hotmail.com","extRef":"12345678-1234-MNOP-1234-ABCDEFGHIJKL","language":"EN","embeddedData":{"appLoginInd":"Y","ubiStatus":"FORMER UBI","postalCode":"H0H 0H0","provinceCode":"QC"},"transactionData":{"distributor":"BEL","transactionDate":"2023-01-18","transactionType":"AUTOMOD","customerTenure":11,"policyNumber":"10000010","policyType":"AUP","cntrct_ref_nbr":11111110,"dim_pl_cntrct_ver_key":10000010,"productHolding":"AUP","agentId":"RRRRRR10","agentEmployeeNbr":"777710","agentName":"mr10 unknow10","agentEmail":"someone@hotmail.com","agentManager":"Mg10 Audi","managerEmail":"someone@hotmail.com","agentTeamCd":"AAAA10","agentHireDt":"2022-06-13"}}
;;;;

filename myjson temp;
data _null_;
  infile mylines end=eof;
  file myjson;
  if _n_=1 then put '{"datalines":[';
  input;
  put _infile_;
  if eof then put ']}';
run;

libname myjson JSON ;
proc copy inlib=myjson outlib=work;
run;

1065  proc copy inlib=myjson outlib=work;
1066  run;

NOTE: Copying MYJSON.ALLDATA to WORK.ALLDATA (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 290 observations read from the data set MYJSON.ALLDATA.
NOTE: The data set WORK.ALLDATA has 290 observations and 6 variables.
NOTE: Copying MYJSON.DATALINES to WORK.DATALINES (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 10 observations read from the data set MYJSON.DATALINES.
NOTE: The data set WORK.DATALINES has 10 observations and 7 variables.
NOTE: Copying MYJSON.DATALINES_EMBEDDEDDATA to WORK.DATALINES_EMBEDDEDDATA (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 10 observations read from the data set MYJSON.DATALINES_EMBEDDEDDATA.
NOTE: The data set WORK.DATALINES_EMBEDDEDDATA has 10 observations and 6 variables.
NOTE: Copying MYJSON.DATALINES_TRANSACTIONDATA to WORK.DATALINES_TRANSACTIONDATA (memtype=DATA).
NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used.
NOTE: There were 10 observations read from the data set MYJSON.DATALINES_TRANSACTIONDATA.
NOTE: The data set WORK.DATALINES_TRANSACTIONDATA has 10 observations and 19 variables.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds