Programming the statistical procedures from SAS

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 217
Accepted Solution

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

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.


Accepted Solutions
Solution
‎08-13-2014 02:54 PM
Regular Contributor
Posts: 217

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

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;

View solution in original post


All Replies
Super User
Posts: 18,567

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

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

Regular Contributor
Posts: 217

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

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;

Respected Advisor
Posts: 3,780

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

Maybe you could also test XLS. 

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

Regular Contributor
Posts: 217

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

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.

Solution
‎08-13-2014 02:54 PM
Regular Contributor
Posts: 217

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

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 313 views
  • 3 likes
  • 3 in conversation