maintain sas formats when exporting to xlsx

[Anita_n]

Hi,

I have a large data set which I want to export to excel (xlsx)

I realise that the formats are changed in excel. I tried using

ods destination but it gives an error message saying not enough memory.

Any help?

ods excel file='mypath\test.xlsx' ;
proc print data=mydata;
run;
ods excel close;

[Kurt_Bremser]

Write your data to a csv file with a data step (use the semicolon as delimiter), and use the formats you want; then the only issue can come from what Excel thinks is in the data. The thinking of Excel is best described with Maxim 31.

[Anita_n]

Okay, thanks

[Kurt_Bremser]

The nice thing about text files is that you can always look at what you created (when writing from SAS) with a simple text viewer/editor. You can't look at an xlsx file (apart from opening it with Excel or another piece of Office software), unless you unpack the archive and try to make sense of the XML data in there, which is not always trivial.

[andreas_lds]

Please show proc contents of the dataset, so that we actually see the formats. It could also be useful to know which sas version you are using.

[Miracle]

Hi @Anita_n .
Will this be useful to you?
Keep the Formats When Exporting to Excel

[Anita_n]

Thanks to you all. I ended up using a different program to export it to excel. Otherwise it wouldn't work. I tried all suggested ways. Am very grateful for your frequent support

[Kurt_Bremser]

Could you please share the solution that finally worked for you, and information about the SAS formats involved?

This will be helpful to anyone else who comes across this thread in the future.

[Anita_n]

I used Stat-Transfer to export the data to excel, because it wasn't working SAS. The Date Variables were always converted back to text, I have a variable with 10 decimal places which I previously reduced with COMMA7.2 to 2decimal places. This was also converted back to 10 decimal places. Those were the problems I encountered using proc export

[Oligolas]

Hi,

here is a way to format your excel output and tell excel to use the formats you want:

data class(label='test data');
set sashelp.class;
retain date 0;
if _N_ eq 1 then date=datepart(today());
else date=date+1;
long_n=age/height/weight**2;
format date is8601da.;
run;
ods excel file='mypath\test.xlsx' ;
   PROC REPORT data=class nowindows split='$'
      style(header)=[font=(Arial, 10pt, bold) just=c verticalalign=m protectspecialchars=on borderstyle=solid borderwidth=1pt bordercolor=lightgray]
      style(column)=[font=(Arial, 10pt)       just=c verticalalign=m protectspecialchars=on borderstyle=solid borderwidth=1pt bordercolor=lightgray]
      ;
      COLUMN name sex age height weight date long_n;

      DEFINE name      / order   style=[just=l cellwidth=7.0cm tagattr='type:String format:Text wrap:yes'] style(header)=[just=l] "Name";
      DEFINE sex       / display style=[just=l cellwidth=6.0cm tagattr='type:String format:Text wrap:yes'] style(header)=[just=l] "Sex";
      DEFINE age       / display style=[just=r cellwidth=2.5cm tagattr='format:0000'] style(header)=[just=c] "A$g$e";
      DEFINE height    / display style=[just=r cellwidth=2.5cm tagattr='format:?/?'] style(header)=[just=c] "Height";
      DEFINE weight    / display style=[just=r cellwidth=2.5cm tagattr='format:[Red][>=100]0.0;[Blue][>=90]0.0;0.0'] style(header)=[just=c] "Weight";
      DEFINE date      / display style=[just=r cellwidth=2.5cm tagattr='type:DateTime format:YYYY-MM-DD'] style(header)=[just=c] "Date";
      DEFINE long_n    / display style=[just=r cellwidth=4.5cm tagattr='format:#0.############################# '] style(header)=[just=c] "Long_n";

      compute before name / style=[font=(Arial, 6pt) just=l background=aqua];
         line ' ';
      endcomp;
   RUN;
ods excel close;

and some links to go even further beyond 😉

http://www.philasug.org/Presentations/201306/ODS_and_Microsoft_Office.pdf (conditional formatting on p.23)

https://exceljet.net/custom-number-formats