topic Re: Read XML URL from SAS EG in SAS Programming

Read XML URL from SAS EG

Billybob73 — Mon, 23 Apr 2018 10:54:50 GMT

Hi,

I have over a 1000 EGP files and i want to change a minor thing in 1 particular program that is part of the EGP project. Every single one of them needs to be changed, so a 1000 changes.

I took the following steps so far :

1. Convert EGP to ZIP

2. Unzip the file

After action number 2. i have a project.XML file which I was hoping to import into SAS and then do the conversion and then output it again to XML, rezip it and then rename it to EGP.

However reading in this XML file gives an error message :

libname local "g:\mylocation";

LIBNAME xmlfile xmlv2 'g:\mylocation\project.xml' XMLTYPE=GENERIC;;

DATA local.project;

SET xmlfile.project;

RUN;

ERROR: PCDATA content found outside column/table boundary. content is false.

ERROR: XML data is not in a format supported natively by the XML libname engine. Files of this type may require an XMLMap to be

input properly.

2 questions :

1. Is my method right ?

2. Why can't i read the XML file ?

Thanks for your help !!

Rgds

Re: Read XML URL from SAS EG

Kurt_Bremser — Mon, 23 Apr 2018 11:16:07 GMT

I'd treat the XML as text (which it is) and use a data step with infile and file to manipulate it.

The next thing I'd do is to remove that program from the egp's and store it on the server to %include it from there. Then you need only one simple edit next time around.

Re: Read XML URL from SAS EG

Billybob73 — Mon, 23 Apr 2018 13:10:14 GMT

Hi Kurt,

This sounds like a great idea, however when

- I treat is as txt

- then change something in the txt

- then rename it back to XML

- then zip the XML file

- then rename the zip file to an egp file

the EGP project won't open......

EG says : There was an error reading the project file. The format is invalid or not correct.

Now when i unpacked the EGP file, there was also a "result.txt" file. Should I also involve this one again ?

Thanks

Re: Read XML URL from SAS EG

Billybob73 — Mon, 23 Apr 2018 13:29:30 GMT

It's not because I forgot to re-include the result.txt file in the EGP, so I notice.

Something is wrong about this code. After renaming the XML extension to TXT i perform the following.

Apperently re-exporting this as TXT file and the renaming it to XML causes the XML file to be corrupt.

Not sure how to proceed now....

%macro changeprog (mysourcefolder, mysourceprog, olditem1, newitem1, olditem2, newitem2, mydestfile, mydestfolder);

data programds1;

infile "&mysourcefolder.\&mysourceprog." dlm = ';';

length progtext $ 30000 ;

input progtext;

run;

data programds2;

set programds1;

progtext = tranwrd(progtext,"&olditem1.","&newitem1.");

progtext = tranwrd(progtext,"&olditem2.","&newitem2.");

run;

%let newdir = %sysfunc(dcreate(&datum ,&mydestfolder.\));

%let filename= "&mydestfolder.\&datum.\&mydestfile..txt";

data _null_;

file &filename.;

set work.programds2;

put (progtext) (+(-1));

run;

%mend;

Re: Read XML URL from SAS EG

Kurt_Bremser — Mon, 23 Apr 2018 13:37:47 GMT

Disclaimer: I have no clue about the "real" structure of a .egp file. I know I can unzip it and get its contents, if a problem arises. I've never built one "manually".

One guess would be that you need to alter another piece of information in the whole archive for it to work.

Or you need to use one special compression method only so that EG recognizes the file correctly.

Since we keep no production programs in EG projects, but have all code in .sas files in the versioning system, similar problems don't arise here. I only unzip projects if they were corrupted during storing and we need to retrieve as much as we can for the end-user.

Re: Read XML URL from SAS EG

Billybob73 — Mon, 23 Apr 2018 14:01:01 GMT

The solution lies in how to read a txt file keeping every single line in a dataset record.

For instance if I have this :

            </SubmitableElement>
            <CodeTask>
                <IncludeWrapper>True</IncludeWrapper>
                <Embedded>False</Embedded>
                <DNA><DNA>
<Type>LocalFile</Type>
<Name>testprog13_met_EURTLD.sas</Name>
<Version>0</Version>
<Assembly />
<Factory />

How to make sure that read them into a dataset containing 10 records ?

At the moment I'm not reading correctly. This line :

<?xml version="1.0" encoding="utf-16"?>

is converted to

<?xml

Can't figure out why..

Rgds

Re: Read XML URL from SAS EG

Kurt_Bremser — Mon, 23 Apr 2018 14:10:29 GMT

The simplest way to catch the whole input line is this:

data want;
infile "have.xml";
length line $100;
input;
line = _infile_;
run;

Adjust length and a lrecl= infile option if needed.

Re: Read XML URL from SAS EG

Satish_Parida — Mon, 23 Apr 2018 14:38:14 GMT

Use SAS XML mapper to create a mapper file for your XML. It is clear from the log your XML file is not in standard XML format

filename xml_lib "/path/filename.xml";
filename MAP "/mapfile_path/YOUR_MAPER.map";

libname xml_lib xmlv2 xmlmap=MAP;   /*Creates the XML library*/

option replace;

proc copy in=xml_lib out=work memtype=data;   /*Copies all XML tables to work*/
run;

Now use SQL join or Data step Merge to create your final table from the downloaded tables in PROC COPY.

Or You can Create a Single mapper using replace functionality to create your data set without any additional join.

Same mapper can be then used to create your output XML file after manipulation.

Documents:

http://www2.sas.com/proceedings/forum2008/099-2008.pdf

http://documentation.sas.com/?docsetId=engxml&docsetTarget=p0194ofvsm9jw0n16bo5atjn33f3.htm&docsetVersion=9.4&locale=en

Let us know if it helped.

Re: Read XML URL from SAS EG

Billybob73 — Mon, 23 Apr 2018 16:23:41 GMT

Hi Kurt,

Thanks for this ! This code is reading into the sas dataset with the blanks included, which is good !

What if I changed some code in the resulting SAS dataset and then re-export it as a txt file.

How would i get the blanks again in the TXT file ? I presume this is what is blocking the opening of the XML file.

I'm getting everything left aligned.........

No luck with this :

data _null_;

file &filename.;

set work.programds2;

put (progtext) (+(-1));

run;

Thanks!

Re: Read XML URL from SAS EG

Kurt_Bremser — Tue, 24 Apr 2018 07:38:07 GMT

I'd tackle this problem in two steps:

First, make a manual change to the XML and see if the manual change (done with the Windows Editor) is accepted by EG after re-zipping.

Only when you know how to do it manually and that it works, you can start to automate things. The manual method also lets you test what needs to be preserved (like trailing blanks or similar).

Then, look for a tool that accomplishes the automated change. On UNIX I'd try to unzip, run awk on the XML in question, and then re-zip. UNIX tools are always good for scripting and automating. SAS might not be the right tool in the end.