sas import data eclosed by {} delimited by ;

Question

I have a csv file delimited by semicolon but I have a field with semicolons inside. It it delimited by {}.

An example of the field (it's name is "neighborhood") would be:

{"T":0,"N":"jardim Atlantico","I":0}

But in some lines I could have:

{"T":0,"I":0,"N":"JD";"Sorocaba parque"}

So when I run

proc import 
    datafile='D:\nnl_muest_result_bs.csv'
     dbms=dlm
    out=muest_Re.I03_nnl_result_bs;
     delimiter=';';
    guessingrows=32767;
run;

It splits by the semicolon, which is ok for the rest of the fields but not in this one.

How can I tell SAS to split using semicolon but to ignore those inside curly braces {}??

Thanks

Answer 1

I haven't found anything useful with proc import but you can solve the problem with a more manual approach. I created some test data:

a;b;n{c;d}e;g
aa;bb;mm{cc;dd;ee}ff;h

and here is the code where the commented part has to be modified according to your variables as is the length of record (and you maybe need to add a lrecl= and firstobs=2 in the infile statement)

data test;
infile "C:\Dati\EsempiSAS\graffe.txt" pad missover;
input @1 record $CHAR100.;
piece1=substr(record,1,indexc(record,"{")-1);
piece2=substr(record,indexc(record,"{")+1,(indexc(record,"}"))-(indexc(record,"{")+1));
piece3=substr(record,indexc(record,"}")+1);
/*
var1=scan(piece1,1,";");
var2=scan(piece1,2,";");
var3=scan(piece1,3,";");
var4=piece2;
var5=scan(piece3,1,";");
var6=scan(piece3,2,";");
*/
run;