when output excel file the period "." was replace by zero automatically in PROC REPORT

master_jiang — Fri, 18 Jun 2021 03:49:10 GMT

Hi All,

I met a problem which confused me a lot. I'm using ods option and PROC REPORT to output a dataset in excel format. However, in my dataset, I assign a variable with period "." as missing value. The variable is character and it contains two values such as "1500" and ".". When proc report process done, in SAS EG output window, the result display as . and 1500 while in excel file, it display as 0 and 1500. Attachment are the snaps.

Snap1.png is the screenshot of result window in SAS EG ,variable cc is displayed as period and 1500

Snap2.png is the screenshot of reporint dataset, variable dose_act(which is cc) is shown as . and 1500 for charater variable

Snap3.png is the screenshot of output excel file, variable dose_act is shown as 0 and 1500.

By the way, in snap3.png there are two more variables LIMIT and CENS, these two variables also contains period as charater variable but in excel file can display as . also

Re: when output excel file the period "." was replace by zero automatically in PROC REPORT

master_jiang — Fri, 18 Jun 2021 03:49:52 GMT

ods listing close;
ods excel file = "&path..xlsx"
options(sheet_name="1-PK"
frozen_headers="on"
frozen_rowheaders="1"
autofilter="ALL"
embedded_titles="ON"
EMBEDDED_FOOTNOTES="ON");
title;
footnote;

proc report data=pk1 nowd missing headline split="~" style(header)=[bordertopwidth=0pt borderbottomwidth=0pt] style(column)=[just=c];
column obs id time nominal_tafd dose_act limit cens;
define obs / style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "Obs";
define id / style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "ID";
define time / style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "aa";
define nominal_tafd / style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "bb";
define dose_act/ style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "cc";
define limit / style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "LIMIT";
define cens / style(column)=[just=L] style(header)=[borderbottomwidth=0.1pt just=L] "CENS";
run;

ods excel close;

Above is my code, cheers

topic when output excel file the period "." was replace by zero automatically in PROC REPORT in SAS Programming

when output excel file the period "." was replace by zero automatically in PROC REPORT

Re: when output excel file the period "." was replace by zero automatically in PROC REPORT