export to an excel file

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

export to an excel file

Hi,

I would like to export my results from proc means procedure to an excel file.

I found a code that would do, but it does not work.

ODS TAGSETS.EXCELXP FILE='C:\TEMP.xlsx'style=minimal;

OPTIONS(Orientation = 'landscape'

FitToPage= 'yes'

Pages_FitWidth= '1'

Pages_FitHeight= '100');

procmeans data=abc mean p1 p25p50 p75 p99STD;

var ROA SIZE RND LEV SGA MTB LOSS NI;

run;

Thanks


Accepted Solutions
Solution
‎11-10-2014 07:38 PM
Super User
Posts: 3,233

Re: export to an excel file

If you want to avoid the Excel error message, change the name of the Excel spreadsheet to TEMP.xml.

You could also try a test proc first to see if your ODS stuff works OK: proc print data = sashelp.class; run;

View solution in original post


All Replies
Super Contributor
Posts: 297

Re: export to an excel file

You need to be more specific about "it doesn't work", however, your OPTIONS statement needs to be inside the ODS statement as such:

ODS TAGSETS.EXCELXP OPTIONS(ORIENTATION = 'LANDSCAPE' FITTOPAGE= 'YES' PAGES_FITWIDTH= '1' PAGES_FITHEIGHT= '100') FILE="&EXPORTLOCA.TEMP.XLSX" STYLE=MINIMAL;

Contributor
Posts: 23

Re: export to an excel file

Sorry about that.

I get a message from excel saying "excel cannot open the file because
the file format or file extension is not valid. Verify that the file has not
been corrupted and that the file extension matches the format of the
file".

Can you help me?

Super Contributor
Posts: 297

Re: export to an excel file

Ok cool.  This is because the resulting file although called something.xlsx is not native excel, but XML.

Excel recongnises this and presents this message.  You just click yes and it will take you into the spreadsheet.

Contributor
Posts: 23

Re: export to an excel file

I clicked ok. but I still get a blank excel file..

Super User
Posts: 19,033

Re: export to an excel file

Did you close the ODS destination before you tried to open the file? I don't see a statement like below in your code.

ODS TAGSETS.EXCELXP CLOSE;


Note that this isn't a native Excel file, but an XML file that Excel can read.

Contributor
Posts: 23

Re: export to an excel file

Yes. I close the ODS destination. Below codes are the code I have been using.

ODS TAGSETS.EXCELXP OPTIONS (Orientation = 'landscape'


'yes'


'1'


'100')FILE='C:\Fall2014\TEMP.xlsx' style=minimal;



title1 'PRE_IPO_2007_2013';


proc means data=IPO_comp_pre mean p1 p25 p50 p75 p99 STD;


var ROA SIZE RND LEV SGA MTB LOSS NI  ;


run;


TAGSETS.EXCELXP CLOSE;

Super User
Posts: 19,033

Re: export to an excel file

Post your log. and note what version of tag sets you're using from the log as well.

Make sure you have the latest version from the website.

Base SAS: ODS MARKUP

Does the following generate an Excel file that opens?

ODS TAGSETS.EXCELXP FILE='C:\Fall2014\TEMP.xlsx' style=minimal;



title1 'PRE_IPO_2007_2013';


proc means data=sashelp.class mean p1 p25 p50 p75 p99 STD


run;


ODS TAGSETS.EXCELXP CLOSE;

Solution
‎11-10-2014 07:38 PM
Super User
Posts: 3,233

Re: export to an excel file

If you want to avoid the Excel error message, change the name of the Excel spreadsheet to TEMP.xml.

You could also try a test proc first to see if your ODS stuff works OK: proc print data = sashelp.class; run;

Contributor
Posts: 23

Re: export to an excel file

Thanks!! It works, but all the values are in the same cell.

for example, means for all different variables are in the one cell.

do you know how to seperate into different rows?

Super User
Posts: 3,233

Re: export to an excel file

Does the PROC PRINT work OK? Also if ODS is causing problems, check to see if you are using the latest EXCELXP tagset which I think is V1.130 - this is reported in the SAS log.

Contributor
Posts: 23

Re: export to an excel file

I ran proc print and it works well with different rows and column but my proc means statement put means of different variables in the same row, not in different rows. How can i use the latest excelxp tagset? mine is v1.122

1398      ODS TAGSETS.EXCELXP CLOSE;


1399


1400      ODS TAGSETS.EXCELXP OPTIONS (Orientation = 'landscape'


1401      FitToPage = 'yes'


1402      Pages_FitWidth = '1'


1403      Pages_FitHeight = '100')FILE='C:\Fall2014\TEMP.xml' style=minimal;


NOTE: Writing TAGSETS.EXCELXP Body file: C:\Fall2014\TEMP.xml


NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.122,


01/04/2011). Add options(doc='help') to the ods statement for more information.


1404


1405      title1 'PRE_IPO_2007_2013';


1406      proc means data=IPO_comp_pre mean p1 p25 p50 p75 p99 STD;


1407      var ROA SIZE RND LEV SGA MTB LOSS NI  ;


1408      run;

Super User
Posts: 3,233

Re: export to an excel file

Go to Reeza's link in her post to get the latest tagset.

🔒 This topic is solved and locked.

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

Discussion stats
  • 12 replies
  • 1171 views
  • 1 like
  • 4 in conversation