How should I use a mapper file to import an xml file into a sas dataset?

[Ethan1]

This xml file comes from 'Title.xlsx'. In order to import this xml file, I have created a mapper file(test.map). 

SAS code:
filename  xlsx2xml '~\xml-sas\Titles222.xml';
filename  SXLEMAP '~\xml-sas\test.map';
libname   xlsx2xml xmlv2 xmlmap=SXLEMAP access=READONLY;

data sheet;
  set xlsx2xml.final;
run;

The dataset I got is shown as data.png. I did some simple data processing to get what I want in the end like data1.png.

My question is, is there a way to add some code to the mapper file(test.map), so that I do not need these data processing in sas
and can directly get the dataset I want (data1.png)?

  

[Kurt_Bremser · Accepted solution]

You are over-complicating things. I got your intended result by doing this:

libname titles xlsx '/folders/myfolders/Titles.xlsx';

data want;
length sheet $32;
set titles.sheet: indsname=dname;
sheet = scan(dname,2,'.');
run;