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.
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;
Can you elaborate a bit more on Copy/Save As?
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;
Maybe you could also test XLS.
Also why not leave EXCEL out completely and create a CSV? I expect MINITAB can read CSV.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.