topic Re: maintain sas formats when exporting to xlsx in SAS Programming

maintain sas formats when exporting to xlsx

Anita_n — Tue, 26 Jan 2021 09:45:20 GMT

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;

Re: maintain sas formats when exporting to xlsx

Kurt_Bremser — Tue, 26 Jan 2021 09:53:00 GMT

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.

Re: maintain sas formats when exporting to xlsx

Anita_n — Tue, 26 Jan 2021 10:35:25 GMT

Okay, thanks

Re: maintain sas formats when exporting to xlsx

Kurt_Bremser — Tue, 26 Jan 2021 10:47:53 GMT

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.

Re: maintain sas formats when exporting to xlsx

andreas_lds — Tue, 26 Jan 2021 11:11:52 GMT

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.

Re: maintain sas formats when exporting to xlsx

Miracle — Tue, 26 Jan 2021 13:17:18 GMT

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

Re: maintain sas formats when exporting to xlsx

Anita_n — Tue, 26 Jan 2021 13:55:31 GMT

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

Re: maintain sas formats when exporting to xlsx

Kurt_Bremser — Tue, 26 Jan 2021 14:14:04 GMT

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.

Re: maintain sas formats when exporting to xlsx

Oligolas — Tue, 26 Jan 2021 14:46:18 GMT

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

Re: maintain sas formats when exporting to xlsx

Anita_n — Tue, 26 Jan 2021 14:48:49 GMT

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