I have used the code below to export 3 data set in excel:
%macro export(dst=);
proc export data=mywork.&dst
outfile="h:\My Documents\MyFile\SAS\MyWork\Result.XLS"
Label dbms=xls Replace;
sheet=" ";
run;
%mend;
%export(dst=Comparison);
%export(dst=Test1);
%export(dst=Test2);
The log summarize successfull but i am unable to open the file Result.xls from my directory. When i try to open it : "Excel found unreadable content in Result.xls.Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes." I clicked yes but it does not open.
Any idea?
Thank you for your help
You can apply a hot fix and use XLSX engine.
Or try changing DBMS to Excel or ExcelCS. You may want to change your sheet names as well, to a dynamic variable otherwise you'll always overwrite the sheet.
%macro export(dst=);
proc export data=mywork.&dst
outfile="h:\My Documents\MyFile\SAS\MyWork\Result.XLSX"
Label dbms=ExcelCS Replace;
sheet="&dst";
run;
%mend;
%export(dst=Comparison);
%export(dst=Test1);
%export(dst=Test2);
I never tried an export where
sheet=" ";
What happens if you actually give a sheet name or comment out this line?
it's still showing the same error message.
How big is your dataset? What version of Excel and SAS are you on?
Excel 2010 and SAS 9.3
The first data set 36440 (6 variables) rows and the two remaining data set are 1 row
You can apply a hot fix and use XLSX engine.
Or try changing DBMS to Excel or ExcelCS. You may want to change your sheet names as well, to a dynamic variable otherwise you'll always overwrite the sheet.
%macro export(dst=);
proc export data=mywork.&dst
outfile="h:\My Documents\MyFile\SAS\MyWork\Result.XLSX"
Label dbms=ExcelCS Replace;
sheet="&dst";
run;
%mend;
%export(dst=Comparison);
%export(dst=Test1);
%export(dst=Test2);
I could not apply the hot fix. So i tried the last option and i go this error message: Failed to connect to the Server
Is there a way we work around the idea in Post 6? thanks!
Did you try DBMS=EXCEL as well?
Sorry, long day. it did work with Excel. Thanks a lot.
How many variables in any of those set?
How many observations?
If you set DBMS=CSV do you get the same problem?(don't use sheet option with csv)
Did you ever used the LIBNAME Excel approach? SAS/ACCESS(R) 9.3 Interface to PC Files: Reference
Sheets are the same as the SAS-datasets.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.