topic Re: How to keep the original numeric format while converting SAS dataset to an Excel file in SAS Enterprise Guide

How to keep the original numeric format while converting SAS dataset to an Excel file

alepage — Tue, 07 Oct 2025 22:28:07 GMT

proc sql;
create table cf_HYFI&minyear.&minmonth._&maxyear.&maxmonth._N_Harmony as
select    a.years as year,
          a.cie,
          a.province_cd,
          a.distribution,
          a.group as LineOfBusiness,
          a.SAS_WP_corr format=NLNUM15.0,
          a.SAS_WU_corr format=NLNUM15.0,
          b.writtenPremium format=NLNUM15.0,
          b.writtenUnit format=NLNUM15.0,
          (a.SAS_WP_corr - b.writtenPremium)*100/a.SAS_WP_corr as wp_diff format= 8.2,
          (a.SAS_WU_corr - b.writtenUnit)*100/a.SAS_WU_corr as wu_diff format= 8.2
from SAS_Summary as a
inner join harmony_trx_summ_2 as b on(a.years=put(b.accountingYear,4.) and a.cie=b.cie and a.province_cd=b.province 
and a.distribution=b.distribution and a.group=upcase(b.lineOfBusiness)) ;

quit;

libname myexcel xlsx "/finsys/bicoe/Temp/Alain/HYFIprep/cf_SAS_HYFI_N_Harmony.xlsx";

  data myexcel.cf_SAS_HYFI_N_Harmony;
  set cf_HYFI&minyear.&minmonth._&maxyear.&maxmonth._N_Harmony;
  run;

Re: How to keep the original numeric format while converting SAS dataset to an Excel file

Tom — Tue, 07 Oct 2025 23:06:51 GMT

The XLSX engine will just copy the data.

If you want to be able to tell EXCEL how to display the values you can use ODS EXCEL and the STYLE attribute. You will need to know how to specify the display format in Excel's terminology.

See this blog post from a few years ago:

https://blogs.sas.com/content/sgf/2022/10/21/formatting-your-microsoft-excel-output-using-ods-excel/#:~:text=Getting%20the%20desired%20formatting%20for,character%20or%20numeric%20in%20SAS.

Re: How to keep the original numeric format while converting SAS dataset to an Excel file

quickbluefish — Wed, 08 Oct 2025 02:04:14 GMT

Are you trying to display numbers using decimals as the thousands separator? If so, the very hacky way to do this is to just use the PUT() function to convert to a character string. Normally, Excel will undo this (automatically interpreting anything that looks like a number as a number), but it won't do this in the case of a number formatted like this:

824.586.182,15

...assuming you're opening it in a locale where the above is not the standard.

options locale=German_Germany;

data test;
num=198481788.01;
numc=put(num, nlnum15.0);
run;

proc export data=test dbms=xlsx replace
	outfile="/home/xxxxx/testnum.xlsx";
run;