Problem while exporting to xml file using ODS

[Vigneswar]

Hi,

I am trying to export into xml file using ods tagset. I am providing the output file name by referring a macro variable.

Below is the code,

%macro Export(output1=, input1=, output2=, input2=, output3=, input3=);

%let t=%sysfunc(today());
%let monday=%sysfunc(intnx(week.2,&t,0,b),date9.);
%let outputdate=%substr(&monday,3,3)_%substr(&monday,1,2)_%substr(&monday,6);
%put &outputdate;

%let Bus = %scan(&output1.,1,_);
%put &Bus;

%let Business = %scan(&Bus,2,.);
%put &Business;

%let file = &Business._CM_ &outputdate;

%put &file;

ods tagsets.ExcelXP file="/sasconfig/Lev1/External/&file.xml" style=meadow
options (sheet_name = "&input1.");

So my file name is resolving to Canada_CM_ JAN_28_2019. But my output file is so different and I have attached screenshot of the output file.

When I modify the file withing brackets as below, I am getting the output xml file as (Canada_CM_ JAN_28_2019)

ods tagsets.ExcelXP file="/sasconfig/Lev1/External/(&file).xml" style=meadow
options (sheet_name = "&input1.");

But I want to export the xml file as Canada_CM_ JAN_28_2019 only.

Any suggestions

Thanks in advance!!

Vigneswar

[Kurt_Bremser · Accepted solution]

When you do this:

ods tagsets.ExcelXP file="/sasconfig/Lev1/External/&file.xml" style=meadow

the dot immediately before the xml extension is "eaten" by the resolution of the macro variable.

Remember Maxim 48?

Do this:

ods tagsets.ExcelXP file="/sasconfig/Lev1/External/&file..xml" style=meadow

One dot ends the macro variable reference, the second dot ends up in the filename.

[Vigneswar]

KurtBreamser,

Yep, you are right and the changes worked..

Thanks a lot !

[Kurt_Bremser]

Small hint: when you want to adress another poster personally, just hit "@", and a list of all participants in the current thread will pop up. Select the one you want, and he/she will end up like this:

@Vigneswar 

You avoid any misspelling, and the user is notified per mail. You can even "call out" any of the registered users, if you enter enough characters of the username for a positive identification after the "@".

[Vigneswar]

@Kurt_Bremser yeah sure!! Thanks for the tip.