BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jwillis
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jwillis
Quartz | Level 8

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

5 REPLIES 5
Reeza
Super User

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

jwillis
Quartz | Level 8

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;

data_null__
Jade | Level 19

Maybe you could also test XLS. 

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

jwillis
Quartz | Level 8

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.

jwillis
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

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