topic Re: How do I Read Multiple Delimited Single line file in New SAS User

How do I Read Multiple Delimited Single line file

anandrc — Tue, 06 Nov 2018 16:37:16 GMT

Hello, I have a text file with data in single line and with multiple delimiter. The file starts with [ and ends with ]. The file is huge but An example is shown below.

Can we read this formatted file in SAS? Any recommendation is appreciated.

[{"seq":24425,"acc":"1-1951","Date":"26-08-2018"},{"seq":24426,"acc":"1-1952","Date":"27-08-2018"},{"seq":24427,"acc":"1-1953","Date":"28-08-2018"}]

Ideally, the sas dataset should be as

seq acc date

24425 1-1951 26-08-2018

24426 1-1952 27-08-2018

24427 1-1953 28-08-2018

Thanks in advance

Re: How do I Read Multiple Delimited Single line file

ballardw — Tue, 06 Nov 2018 16:49:07 GMT

If that is JSON then you should look at the LIBNAME JSON statement and options. It may say you many headaches.

Re: How do I Read Multiple Delimited Single line file

Tom — Tue, 06 Nov 2018 16:51:39 GMT

That looks like JSON. Try reading it with PROC JSON.

If every record has every field then just read directly.

data tall ;
  row+1;
  do col=1 to 3;
    length name $32 value $50 ;
    infile cards dlm='[]{},:' ;
    input name value @@;
    name=dequote(name);
    value=dequote(value);
    output;
  end;
cards4;
[{"seq":24425,"acc":"1-1951","Date":"26-08-2018"},{"seq":24426,"acc":"1-1952","Date":"27-08-2018"},{"seq":24427,"acc":"1-1953","Date":"28-08-2018"}]
;;;;

proc print; run;

Obs    row    col    name    value

 1      1      1     seq     24425
 2      1      2     acc     1-1951
 3      1      3     Date    26-08-2018
 4      2      1     seq     24426
 5      2      2     acc     1-1952
 6      2      3     Date    27-08-2018
 7      3      1     seq     24427
 8      3      2     acc     1-1953
 9      3      3     Date    28-08-2018

Re: How do I Read Multiple Delimited Single line file

BillM_SAS — Tue, 06 Nov 2018 17:41:35 GMT

The JSON procedure is output only. To read in that JSON looking data, try using the JSON LIBNAME statement, as others have suggested.

Re: How do I Read Multiple Delimited Single line file

BillM_SAS — Tue, 06 Nov 2018 17:55:09 GMT

I put your example data line into the file json.txt and used it in the JSON LIBNAME statement.

libname test json ".\sasuser\json.txt" 
                  map=".\sasuser\json.map" automap=reuse;
run;

proc print data=test.root(drop=ordinal_root); 
run;

The results are what you desired:

Obs	seq	acc	Date
1	24425	1-1951	26-08-2018
2	24426	1-1952	27-08-2018
3	24427	1-1953	28-08-2018

Re: How do I Read Multiple Delimited Single line file

anandrc — Wed, 07 Nov 2018 15:21:39 GMT

Thanks for your replies.

Tried the above code and getting an error as

"The JSON engine cannot be found."

We are currently using SAS 9.3

Re: How do I Read Multiple Delimited Single line file

BillM_SAS — Wed, 07 Nov 2018 15:35:46 GMT

Sorry. I forgot to mention you need SAS 9.4 maintenance 4 or better to have access to the JSON engine.

Re: How do I Read Multiple Delimited Single line file

anandrc — Sat, 10 Nov 2018 09:06:33 GMT

We are upgrading to SAS 9.4. I will try the solution once we have 9.4. But thank you all for your assistance.
Much appreciated.