Tagsets Excelxp - Error

[Pritish]

Hi,

I am trying to export data from SAS dataset to Excel 2007 and I am having the following code to create excel sheet. This macro create the excel file but when I try to open it, it gives me an error saying "File cannot be opened because of erros. Errors are listed in 'Path\44334323.log" file"

Has anyone faced similar issue before, if so, how did you solve it?

Code:

%Macro ABC /parmbuff;

ods listing close;

%put syspbuff contains: &syspbuff;

%let num=1;

%let dsname = %scan(&syspbuff,&num); /* Dataset name */

%let pid = %substr(&dsname,6);       /* Plan ID ex: 01 or 802 */

%let strata = %substr(&dsname, 1,1); /* Strata Number ex: 1 */

%let plannum = %substr(&dsname,2,5); /* Plan number ex: h9990*/

ods tagsets.excelxp file = "A:\ABC.xls" style = sansprinter;

%do %while(&dsname ne);

ods tagsets.excelxp options (sheet_name = %quote("Strata&Strata"));

proc print data = TT.Sample_Strata&dsname.pbp&pid. noobs;

run;

%let num = %eval(&num+1);

%let dsname = %scan(&syspbuff, &num);

%end;

ods tagsets.excelxp close;

%mend ABC;

%ABC(1h999001);

%ABC(1h999001, 1h888902);

Thanks in advance.

I am using SAS 9.3

[ballardw]

I think you may be getting a problem with the Sheet_name= part. Did you try sheet_name="Strata&strata"?

I think using %quote it may be trying to name a sheet "Strata1" instead of Strata1.

[Pritish]

I tried that, but didn't work.

[Cynthia_sas]

Hi:

  I'm not sure what's going on with your macro and program. I believe that you should open a track with Tech Support on that. However, with a simplified version of your macro, I do get the sheet names working as expected. I don't find the need for %QUOTE, as the simple use of the macro variable reference within double quotes works just fine.

  So I think that it's either something mis-coded in your macro code or something wrong with the dataset or information that you're trying to read out of %SYSPBUFF. I'm not actually sure I understand all your code, since you did not explain the datasets you were reading and I'm not sure why you're using PARMBUFF and %SYSPBUFF, in the first place. It seems to me you are trying to bypass having either positional parameters or keyword parameters.

  But since the overall technique does work to name each sheet differently, I think that your issue is with something else.

cynthia

%Macro testage;

ods listing close;

ods tagsets.excelxp file = "c:\temp\ABC.xls" style = sansprinter;

%do age = 11 %to 13;

ods tagsets.excelxp options (sheet_name = "sheet&age");

proc print data = sashelp.class;

  where age = &age;

run;

%end;

ods tagsets.excelxp close;

%mend testage;

%testage()

[Pritish]

Cynthia,

Since I have varying number of parameters in my macro, I have used PARMBUFF / %SYSPBUFF option in my macro.

Well, I have got different datasets containing information about my customers. So what I am trying to do is read all those datasets and extract information into an excel file. Now, in my macro call, I may ask my ask to read 2 dataset at one time and 5 dataset at another time.

As far as %QUOTE is concerned, I removed it from my code and tried to run the macro but again excel file didn't open and instead gave me a error asking me to read some log file. Do you think something should be wrong with Tagsets.excelxp and I should use Proc Export?

Thanks for your help and support!

[Cynthia_sas]

Hi:

  I think my program shows that the issue is not with TAGSETS.EXCELXP. With the macro logic simplified, your basic process flow does work in my example (start TAGSETS.EXCELXP, then loop over a group of datasets to make a sheet from PROC PRINT for each dataset),  Generally, when Excel complains about not being able to open output, it is because the XML got corrupted somehow by something in your program. I think there's something wrong with your data or the way you are creating macro variables from your input parameters. If you open a track with Tech Support, they can look at all your code, all your data, at the logs from your program and help you figure out where your macro is going wrong.

cynthia

[Pritish]

I will give it one more try and see if I can solve the issue. If not, I will contact Tech Support and hope they can find out the problem.

Thanks!

[Pritish]

Since my data had some inconsistencies and I was not able to open the excel file.

the only problem that I am facing now is excel is not maintaining the format which I have in SAS. For ex: I have a numeric column which is 35 in lengh. But when I take the data, excel is showing me a 34342E+12 .. Can you suggest what method I should take to solve that problem?

[ballardw]

Sometimes Excel shows scientific notation when the column isn't wide enough. I would try making the column wider in Excel. If that works then it may be worth looking into style overides to set the column widths in the proc print code.

[Pritish]

Thanks for suggestion Ballardw.

I used the style option something like this:

proc print data = x ;

var aa / style (data) = {tagattr ="format:@"};

run;

[Pritish]

I am able to generate excel sheet exactly as I want. The only problem that I have is when I open the Excel file, Excel gives a warring that "File you are trying to open is in a different format than specified by the file extension".

Is there a way to bypass or stop that warning message?

your help is really appreciated.

[Cynthia_sas]

Hi:

  This is a known message that Excel puts out, ever since Excel 2007, when Microsoft decided to "harden" file extensions. When you name the file .XLS, Excel checks inside the TAGSETS.EXCELXP file and it finds XML (Spreadsheet Markup Language XML). XML is not proprietary XLS format. So Microsoft issues a somewhat snarky warning message. If you click yes, the file will open with no problems. Or, you can name the file, accurately as a .XML file extension, since that is what the file is. The only reason for using the .XLS extension is to "fool" the Windows registry into launching Excel when you double click on the file name.

  There is a Tech support note about that message (http://support.sas.com/kb/31/956.html). It refers specifically to the MSOFFICE2KX destination, but the information is also relevant to the TAGSETS.EXCELXP destination. There might be a way to change it in the Windows registry. I'm not sure it's worth it.

cynthia

[Pritish]

Cynthia,

Thanks for the information. I think, I would change the excel format to XML rather than messing up the windows registry.

I am not sure if this is an issue, but when I changed the file format to XML and execute the code, I need to open the excel file from SAS before opening from the folder. If I don't open the file as soon as SAS code completes execution and rather go to folder and try to open it, it gives me an error "Converter failed to save the file".

[ballardw]

I get inconsistent behavior when using tagsets.excelxp. Sometimes I get a file save dialog box, sometimes not. I just close them most of the time, especially when creating a series of files.

If you haven't alread you want to use the Windows settings to associate the XML file type default with Excel.

[Lulu_W]

Looks like to me that, the problem is more likely to caused by the underlying output dataset than your ODS procedure, for example, the data might be too large for one cell etc...