How to import a positional text file using XML file

[pedrohtr]

Hi everyone,

I have several data files need to import in EG, that comes like:

TXT FILE

0000280727000004825100000078690010001000005A0000000000000000000000000000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001

XML FILE

<?xml version="1.0" encoding="UTF-8"?>

-<FlatFileDefinition ZipName="" FileName="positional.txt" TemplateName="MMM MIX - v2 Catlg TXT">


-<Columns>

<Column Type="Field" Repeat="1" Length="10" Start="1" Name="Code"/>

<Column Type="Field" Repeat="1" Length="10" Start="11" Name="Ncc"/>

<Column Type="Field" Repeat="1" Length="10" Start="21" Name="Prov_cod"/>

<Column Type="Field" Repeat="1" Length="3" Start="31" Name="Regi_Cod"/>

<Column Type="Field" Repeat="1" Length="4" Start="34" Name="Dist_Cod"/>

<Column Type="Field" Repeat="1" Length="6" Start="38" Name="Terr_Cod"/>

<Column Type="Field" Repeat="1" Length="1" Start="44" Name="Clie_Let"/>

<Column Type="Measure" Repeat="12" Length="15" Start="45" Name="Units_Period"/>

<Column Type="MeasureControl" Repeat="1" Length="25" Start="225" Name="Sum_Total"/>

</Columns>

</FlatFileDefinition>

How to make EG interpret this XML file like the line format/delimiter? Or any other possible solution....

Thanks in advance

[Haris]

For your consideration: open XML in Excel and saving it in Excel format. Then import Excel into SAS.

[ballardw]

Or save as CSV from Excel and import.

[Ksharp]

Use the following to get data structure from xml.
After that make a macro or CALL EXECUTE() to import that TXT file.


filename x temp;
libname xx xmlv2 '/folders/myfolders/have.xml' automap=replace xmlmap=x;
proc copy in=xx out=work;
run;

[Ksharp · Accepted solution]

data have;
infile '/folders/myfolders/have.xml' dlm='"/' ;
input @'Type=' type : $40. @'Repeat=' repeat : $40.
      @'Length=' length : $40. @'Start=' start : $40. 
      @'Name=' name : $40. ;    
run;
data name;
 set have;
 num_repeat=input(repeat,best.);
 num_len=input(length,best.);
 num_start=input(start,best.);
 do i=1 to num_repeat;
  vname=catx('_',name,i);
  col=catx('-',num_start,num_start+num_len);
  num_start=num_start+num_len;
  output;
 end;
run;
proc sql noprint;
select catx(' ',vname,'$',col) into : list separated by ' '
 from name;
quit;
%put &list ;
data want;
 infile '/folders/myfolders/have.txt' lrecl=320000 pad;
 input &list ;
run;

[pedrohtr]

Excelent way to solve Ksharp!
Thanks a lot! Worked fine.