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
If that is JSON then you should look at the LIBNAME JSON statement and options. It may say you many headaches.
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
The JSON procedure is output only. To read in that JSON looking data, try using the JSON LIBNAME statement, as others have suggested.
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 |
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
Sorry. I forgot to mention you need SAS 9.4 maintenance 4 or better to have access to the JSON engine.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.