topic Re: Limit values for import of a XML file in SAS Programming

Limit values for import of a XML file

Debora1 — Mon, 27 Apr 2020 09:35:34 GMT

Good morning!

I have been trying to import a large xml file.

Therefore i checked this document https://support.sas.com/resources/papers/proceedings17/1318-2017.pdf

and tried to use this code:

libname SGFXML xml "/folders/myfolders/cloud/drugbank_all_full_database/fulldatabase.xml";
libname DATA "C:\Documents\Data";
data data.dm;
set sgfxml.dm;
run;

Apparently everything was running fine, however running forever and did not finish the processing of the code (i let for a night), I am currently using my virtual box and sas university edition for windows

The file is not that big 1.3 GB, i am not sure why it is taking so long to process, so what i was thinking is if there is a way to limit the importation rows so that it is faster. Does anybody knows how to do it?

Thanks so much!

Re: Limit values for import of a XML file

Shmuel — Mon, 27 Apr 2020 10:38:01 GMT

Use options obs=10; (or any other number) and check does it import the first 10 observation correctly.

Start with a small number and if it results OK and fast enough change limitation

to 10% of estimated observations. Check running elapse time and compute the estimated time for full run.

If the limitation of 10 observations takes a long time or finish with any errors please post (1) the full log (2) a sample of the xml file.

Re: Limit values for import of a XML file

Ksharp — Mon, 27 Apr 2020 11:10:44 GMT

Did you try XMLV2 engine ?

filename x temp;
libname SGFXML xmlv2 "/folders/myfolders/cloud/drugbank_all_full_database/fulldatabase.xml" automap=replace xmlmap=x ;

proc copy in=SGFXML out=work noclone ;
run;

Re: Limit values for import of a XML file

Debora1 — Mon, 27 Apr 2020 11:22:54 GMT

Hello Shmuel,
thanks for the answer!
I tried, but still no end of compilation.
Of course, the file that i am trying to import is this one:
https://www.drugbank.ca/releases/latest#full

Re: Limit values for import of a XML file

Debora1 — Mon, 27 Apr 2020 11:53:58 GMT

Thanks Ksharp!
I have tried and the following errors appears:
I have checked the errors, but given my limited knowledge on SAS here is what i found:
1) ERROR: The creation of the XML Mapper file failed. ( https://communities.sas.com/t5/SAS-Programming/How-to-import-XML-file-into-SAS/td-p/418527) and I am assuming i need the Microsoft® Windows® for x64 SAS XML Mapper (https://support.sas.com/downloads/package.htm?pid=486#)

I tried to open my xml file, but the following message appears:

2) ERROR: Error in the LIBNAME statement.This one i am assuming is related to the previous erros (https://communities.sas.com/t5/SAS-Data-Management/Error-in-SAS-import-connection-failed-error-in-libname-statement/td-p/212042)

3) ERROR: Libref SGFXML is not assigned. This one i could not understand either.

The database that i am trying to import is this one:

https://www.drugbank.ca/releases/latest#full

"1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
72
73 filename x temp;
74 libname SGFXML xmlv2 "/folders/myfolders/cloud/drugbank_all_full_database/fulldatabase.xml" automap=replace xmlmap=x ;
ERROR: The creation of the XML Mapper file failed.
ERROR: Error in the LIBNAME statement.
75
76 proc copy in=SGFXML out=work noclone ;
77 run;

ERROR: Libref SGFXML is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
real time 0.18 seconds
cpu time 0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
78
79
80 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
92 "

Re: Limit values for import of a XML file

Ksharp — Mon, 27 Apr 2020 12:10:06 GMT

Sorry. Maybe someone could help you .
The following code could check what is your xml file look like.

data _null_;
infile '.........';
input;
put _infile_;
if _n_=100 then stop;
run;

p.s. if could ,write some sas code on your own to parse the xml file , OR resort to the third side software ?

Re: Limit values for import of a XML file

Shmuel — Mon, 27 Apr 2020 12:31:47 GMT

I see that your xml file is from

https://www.drugbank.ca/releases/latest#full

can you download the file to your computer and read it from a local directory?

What sas platform do you use?