BookmarkSubscribeRSS Feed
brophymj
Quartz | Level 8

Hi

I have a dataset in sas with all the fields formatted the way I want but when I export to xlsx using the code below, I lose the formats.

proc export

data = dataset1


outfile = "...\dataset.xlsx"

dbms = xlsx

replace;

sheet = final;
run;

Any way to keep the format?

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

As KurtBremser notes, Excelxp tagset will give you more flexibility in your output files.  Here is an article talking about proc report + tagset + mso formats:

https://communities.sas.com/thread/47133

brophymj
Quartz | Level 8

I want to export to xlsx. I've tried using ODS TAGSETS.EXCELXP as...

ODS tagsets.ExcelXP FILE="...\oneway_output_by_year.xlsx";


PRINT DATA=oneway_all4;

run;

ods tagsets.ExcelXP CLOSE;

When I run this I get an message saying "Do you want to open or save this file". When I select opn, I get a message saying "excel cannot open the file oneway_output_by_year.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted or and that the file extension matches the format of the file.".

Do you know what I'm doing wrong?

Can the export be automated or do you have to save each time manually? 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, I would change the proc print to be a proc report first off.  Also add a style.  Change the file extension to XLS:

ods tagsets.excelxp file="xyz.xls" style=statistical;

proc report data=oneway_all4 nowd;

     columns _all_;

     define ...;

run;

ods tagsets.excelxp close;

Note that the tagset actually creates XML output, which Excel then reads in and interprets.  To also note that in SAS 9.4 there is a libname statement through which you can create native XLSX files: SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition

Before 9.4 you would need to know the Open Office format to create them.

brophymj
Quartz | Level 8

Thank RW9 but I'm still getting that error message when I try to open the file -when I do open it the format it the way I want it but the error message is annoying.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, the "warning" message you get about the file being in a different format than expected is actually an Excel one, not controllable by SAS.  What it is saying is that a file with the extension XLSX is expected by Excel to be in its Open Office format.  What you are creating is XML.  So in reality you would want to create the file as xyz.xml, however on most peoples machines XML will open with a web browser not Excel by default, so by using the .xlsx extension you are fooling Windows into thinking it should open the file with Excel.  You could of course ask everyone to associate XML files to open with Excel and then you would avoid this message, but its probably a lot of effort.  If you have 9.4 then try the libname variety.

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 Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2283 views
  • 0 likes
  • 3 in conversation