topic Re: ods tagsetxp -Error while opening the file in SAS Programming

ods tagsetxp -Error while opening the file

BaalaRaaji — Fri, 18 May 2018 07:02:50 GMT

Hi There,

below is the code am running...i can get the output in excel but while opening its giving the below error

%macro Rep_excel;
ods path(prepend) CDA_USER.templat(update);
ODS LISTING CLOSE;
ODS tagsets.excelxp FILE="\\aunsw0170020\Data\Restricted\PFS\ICC Personal Finance\CCSDept\CCSUser\Data Dashboard.xls"
STYLE= serifprinter
options (FitToPage = 'yes' embedded_titles = 'yes' /*skip_space='0,0,0,0,0' */ missing=' ') ;
ods escapechar="^";

%excelrep(Monthly,Mnt,&curr_date,&prev_date );
%excelrep(Yearly,yr,&curr_date,&last_yr_date);
%excelrep(Quartely,Quart1,&firstq,&secq);
%excelrep(Quartely,Quart2,&thirdq,&fourthq);
ods tagsets.excelxp close;

%mend;
%Rep_excel;

Message i get while opening the file:

The file format and extension of 'Data Dashboard.xls' don't match.The file could be corrupted or unsafe.Unless you trust its source,don't open it.Do you want to open it anyway?

Can help appreciated..

Re: ods tagsetxp -Error while opening the file

BaalaRaaji — Fri, 18 May 2018 07:13:40 GMT

i did tried using xml instead of xls but still its not working...any help is much appreciated.thanks

Re: ods tagsetxp -Error while opening the file

RW9 — Fri, 18 May 2018 07:22:41 GMT

The clue, as always, is given in the warning message (which is by the way an Excel warning).

"The file format and extension of 'Data Dashboard.xls' don't match."

What you should understand, which is unfortunately lost to the mists of time, is that file extensions - the part after the dot - tell the operating system what program to open the file with (file association), and the opening program what the format is. Now:
XLS - this is a proprietary file format created by MS for Excel up to around the 2007 release of Office when they changed to XLSX. This is a very old format, and not good at portability or compatability, and really shouldn't be used any more.

XLSX - this is a proprietary Open Office document file, it is in fact a zip with some XML files in it. It is the default Excel file format now.

Now what you are creating from your ods statement is tagset output. This is XML which has various tags and such like that Excel can read and parse, but it is not an XLS or XLSX file, it is an XML file. Therefore what Excel is telling you is that the file extension is telling me I am to expect a binary XLS file, but instead when I read it, I get a text XML file, this does not match.

Call your file extension the code for what the file is:

ods tagsets.excelxp file="\\aunsw0170020\Data\Restricted\PFS\ICC Personal Finance\CCSDept\CCSUser\Data Dashboard.xml"

Then you will not get this warning. Do note however that due to file associations, .xml may open in browser, or in XML application etc. but you can change this (if you really want to) by right clicking on the file and choosing open with and selecting Excel. If you want to create native XLSX then 9.4 has libname xlsx. And if you want native (aaarrrghhh really bad) XLS format, then use proc export.

Edit:

"i did tried using xml instead of xls but still its not working" - posting it did not work does not help, we need information as to what, how etc.

Re: ods tagsetxp -Error while opening the file

BaalaRaaji — Fri, 18 May 2018 07:32:29 GMT

i am attaching the excel to email and same warning pops up..even when using xlm/xls/xlsx..

title ' ';
filename outbox
email
to=('xxx.y@xxx.com.au')
type='text/html'
subject="Data Driven Alerts &curr_date."
from='xxx.y@xxx.com.au'
attach=("\\Portfolio Analytics\Reporting\Analytics\BPB\Temp_raj\Data Dashboard.xlsx");
ods html body=outbox rs=none;

DATA _NULL_;
File print;
put 'Hi All,';
put ;
put "Please check the attachment for alert criteria for &curr_date.";
put ;
put ;
put 'Regards,';
put 'RRR Team';
put ;
put 'This is an automatically generated email.';
run;

ods html close;

Issue is:I can open the file and output seems all good only problem with the message pop ups.

The file format and extension of 'Data Dashboard.xls' don't match........................

Re: ods tagsetxp -Error while opening the file

RW9 — Fri, 18 May 2018 07:40:11 GMT

I can't tell you anything other than what I have said, your error:

The file format and extension of 'Data Dashboard.xls' don't match.

Tells me that the file is still called xls which it is not, and the code you provide is attaching and XLSX. You need to create an XML file from your ods statement, and attach the XML file to your email.

Although, I really recommend not using email for sending files, file size tends to break email, its not secure, etc.

Re: ods tagsetxp -Error while opening the file

Kurt_Bremser — Fri, 18 May 2018 08:06:10 GMT

@BaalaRaaji wrote:

i am attaching the excel to email and same warning pops up..even when using xlm/xls/xlsx..

title ' ';
filename outbox
email
to=('xxx.y@xxx.com.au')
type='text/html'
subject="Data Driven Alerts &curr_date."
from='xxx.y@xxx.com.au'
attach=("\\Portfolio Analytics\Reporting\Analytics\BPB\Temp_raj\Data Dashboard.xlsx");
ods html body=outbox rs=none;

DATA _NULL_;
File print;
put 'Hi All,';
put ;
put "Please check the attachment for alert criteria for &curr_date.";
put ;
put ;
put 'Regards,';
put 'RRR Team';
put ;
put 'This is an automatically generated email.';
run;

ods html close;

Issue is:I can open the file and output seems all good only problem with the message pop ups.

The file format and extension of 'Data Dashboard.xls' don't match........................

You use file Portfolio Analytics\Reporting\Analytics\BPB\Temp_raj\Data Dashboard.xlsx in the email, WHICH IS NOT CORRECTLY NAMED AS XML!

But then Excel still complains about "Data Dashboard.xls", WHICH IS NOT THE FILE YOU SENT ANYWAY!

Repeating the same mistake again and again CANNOT lead to a different outcome (see Einstein's definition of insanity)

create the output from tagsets.excelxp with a .xml extension
attach exactly that file to the email
make sure that exactly that file is opened in Excel

Re: ods tagsetxp -Error while opening the file

BaalaRaaji — Fri, 18 May 2018 08:22:49 GMT

Kurt Bremser
have tried number of times...by using same extension..either XML/xls/xlsx in the code and also in email part of the code...please advise if you know the solution..

RW9....problem is not with file size..we can use ODs package to archive and to reduce file size (zip)...AS said I have tried xml version too...same warning pop ups...

Re: ods tagsetxp -Error while opening the file

BaalaRaaji — Fri, 18 May 2018 08:25:22 GMT

Not sure if you guys understand the query or not...you can try with the code and can help me to fix the warning msg

Re: ods tagsetxp -Error while opening the file

RW9 — Fri, 18 May 2018 08:33:14 GMT

I will try once more. On your local machine (forget the email part for the time being) run this bit of SAS code:

ods tagsets.excelxp file="<put path here>/test.xls";
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

Change the <put path here> to somewhere local. Then open this test.xls file you saved locally. You will get the warning from Excel.

Now, with exactly the same code:

ods tagsets.excelxp file="<put path here>/test.xml";
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;

Only changing the file extension to xml, then opening the test.xml with Excel, you will not get the warning.

Re: ods tagsetxp -Error while opening the file

Kurt_Bremser — Fri, 18 May 2018 08:46:04 GMT

WHICH code?

You have provided several snippets that use different filenames. Post the whole code you ran in one piece, so we can re-execute it as is.

For reference: I ran this code:

ODS tagsets.excelxp FILE="$HOME/sascommunity/class.xml"
STYLE= serifprinter
options (FitToPage = 'yes' embedded_titles = 'yes' /*skip_space='0,0,0,0,0' */ missing=' ') ;
ods escapechar="^";

proc print data=sashelp.class;
run;

ods tagsets.excelxp close;

filename outbox
email
to=('username@domain.com')
type='text/html'
subject="Test"
attach=("$HOME/sascommunity/class.xml");

ods html file=outbox;

DATA _NULL_;
File print;
put 'Hi All,';
put ;
put ;
put ;
put 'Regards,';
put 'RRR Team';
put ;
put 'This is an automatically generated email.';
run;

ods html close;

filename outbox clear;

(only the email address is changed)

I received that email in my inbox, saved the attachment to my desktop, and opened it with MS Excel (or by using the Office XML Handler) without any problems or messages.

This is the log from the code:

27         ODS tagsets.excelxp FILE="$HOME/sascommunity/class.xml"
28         STYLE= serifprinter
29         options (FitToPage = 'yes' embedded_titles = 'yes' /*skip_space='0,0,0,0,0' */ missing=' ') ;
NOTE: Writing TAGSETS.EXCELXP Body file: $HOME/sascommunity/class.xml
Unrecognized option: MISSING
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015). Add options(doc='help') to the ods 
statement for more information.
30         ods escapechar="^";
31         
32         proc print data=sashelp.class;
33         run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.11 seconds
      cpu time            0.02 seconds
      

34         
35         ods tagsets.excelxp close;
36         
37         filename outbox
38         email
39         to=('username@domain.com')
40         type='text/html'
41         subject="Test"
42         attach=("$HOME/sascommunity/class.xml");
43         
44         ods html file=outbox;
NOTE: Writing HTML Body file: OUTBOX
45         
46         DATA _NULL_;
47         File print;
48         put 'Hi All,';
49         put ;
50         put ;
51         put ;
52         put 'Regards,';
53         put 'RRR Team';
54         put ;
55         put 'This is an automatically generated email.';
56         run;

NOTE: 5 lines were written to file PRINT.
NOTE: The DATA step printed page 2.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
      

57         
58         ods html close;
Message sent
      To:          'username@domain.com'
      Cc:          
      Bcc:         
      Subject:     Test
      Attachments: ( "$HOME/sascommunity/class.xml" )
59         
60         filename outbox clear;
NOTE: Fileref OUTBOX has been deassigned.

once again, only the email address was anonymized.

Re: ods tagsetxp -Error while opening the file

ballardw — Fri, 18 May 2018 14:06:55 GMT

Before I opened this thread I was certain this was going to involve: tagsets.Excelxp and that someone created an output file with an extension other than XML.

XLS is also so old why are you attempting to abuse the data that way?

You might investigate if ODS EXCEL will work for you. If so use it to create XLSX files natively.

Re: ods tagsetxp -Error while opening the file

BaalaRaaji — Mon, 21 May 2018 00:07:35 GMT

following your steps..i have updated with xml formst and can open the file through excel without warnings..cheers for that..

But now again new issue..:-)

I have got 3 proc print output data for each sheets to save by monthly/yearly/quartely... by sheet names...however when i put xml ..its saving into different sheets for each proc print output...monthly1 monthly2 monthly3,yearly1 yearly2,yearly3....

hope you understood..

any thoughts on it..?

Re: ods tagsetxp -Error while opening the file

BaalaRaaji — Mon, 21 May 2018 00:28:56 GMT

i figured it out..its working..thank you all..appreciate your help guys...thanks