topic Re: Have you ever created an Excel file using SAS that could not be read by Minitab? in Statistical Procedures

Have you ever created an Excel file using SAS that could not be read by Minitab?

jwillis — Wed, 13 Aug 2014 14:44:41 GMT

Have you ever created an Excel file using SAS that could not be read by Minitab? If so, how did you fix the problem? A coworker used proc export (dbms=xlsx) to create a two column, 125 row spreadsheet. Minitab will not read the SAS created .xlsx file but will read a (copy/saved as) version of the file. The coworker is using SAS 9.4 on a 64 bit processor. I've googled the internet and searched through SAS but I cannot find any references to the issue. Thank you.

38   data anova_full;
39       do line = 1 to 3;
40           do j = 1 to 50;
41               if line=1 then ct = round(0.1 + 0.09*normal(1),.0001);
42               else if line=2 then ct = round(0.07 + 0.1*normal(1),.0001);
43               else if line=3 then ct = round(0.05 + 0.1*normal(1),.0001);
44               output;
45           end;
46       end;
47
48       drop j;
49   run;

NOTE: The data set WORK.ANOVA_FULL has 150 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.00 seconds

50   proc export
51     data=anova_full
52     dbms=xlsx
53     outfile="e:\testing\trash\anova.xlsx"
54     replace;
55   run;

NOTE: The export data set has 150 observations and 2 variables.
NOTE: "e:\testing\trash\anova.xlsx" file was successfully created.

Re: Have you ever created an Excel file using SAS that could not be read by Minitab?

Reeza — Wed, 13 Aug 2014 14:59:38 GMT

Can you elaborate a bit more on Copy/Save As?

Re: Have you ever created an Excel file using SAS that could not be read by Minitab?

jwillis — Wed, 13 Aug 2014 15:25:23 GMT

Unfortunately, I am unable to elaborate. The message I received was "copied the spreadsheet and it worked (Minitab read the copied spreadsheet)". I have SAS 9.3 and do not have Minitab. There was an issue where popups were displaying when they opened the 'EXPORT' original spreadsheet. I cannot elaborate because I do not have pictures of what I saw and I do not have 9.4, 64bit to replicate what I saw. I did not have any issues opening any of the spreadsheets using 9.3, 32 bit.

I am having the users test the code below now.

libname mylib excel "e:\testing\trash\MyFile testing output.xlsx";
data mylib.testing;
set anova_full;
run;

Re: Have you ever created an Excel file using SAS that could not be read by Minitab?

data_null__ — Wed, 13 Aug 2014 15:38:01 GMT

Maybe you could also test XLS.

Also why not leave EXCEL out completely and create a CSV? I expect MINITAB can read CSV.

Re: Have you ever created an Excel file using SAS that could not be read by Minitab?

jwillis — Wed, 13 Aug 2014 15:46:51 GMT

We tested .xls and .xml. and tagsets.excelxp. I think, but have no proof, that the 9.4 64 bit platform is the issue. I can't test this thought because I only have 9.3 32 bit.

Re: Have you ever created an Excel file using SAS that could not be read by Minitab?

jwillis — Wed, 13 Aug 2014 18:54:13 GMT

Using libname with the EXCEL engine solved the problem but I still do not know the reason why Proc Export causes the problem.

libname mylib excel "e:\testing\trash\MyFile testing output.xlsx";

proc sql; drop table mylib.testing; quit;
data mylib.testing;
set anova_full;
run;

libname mylib clear;