Proc report ODS excel output to remove dots

[SASAna]

SAS Users,

Wanting help with removing dots from Excel output, which is been printed by ODS & PROC report.

Due to different data types & lengths problem occured while appending the data  from many files , i did formatting  (Character to Numeric & Numeric to charcter conversions) and used length & formats to  avoid truncation.

This formatted data is being proc reported & printed to excel by ODS statement. Due to this i see some dots in the excel file.

Is there a way to get change dots to empty cells in the Excel using some options ??

Thanks,

Ana

[collinelliot]

Have you tried this?

options  missing = '';

[collinelliot]

I'm assuming your dots are missing values for numeric variables. If not, can you give an example?

[SASAna]

Yes, Colline, you are right . They could be missing Numbers.

[Reeza]

Try 

option missing = ' ';

[SASAna]

Hi Reeza,

Thank yu.

Some of them they changed dot to empty cell. that was helpful . but some of them are still there. 

I am doing the below logic - 

IF "class" = "'A'" THEN

DO;

AMT1 = put(AMT ,10.);    /*  data type conversion */
NBR1 = put(NBR ,10.);

; END;
ELSE

DO;

AMT1= AMT;    /* for other classess data type conversion not needed */

NBR1 = NBR;

END;

I am renaming them back i the next data step & appending the data by loop  of class.

when i am appending the data for class A , B, C, D,E, F , G and doing ODS excel.. I am seeing the dots.

Thanks,

Ana

[Reeza]

Figure out where in your process you're adding the dots. If a character variable has a dot, then SAS will print a dot because it's a character. 

You need to ensure that the PUT conversion to character is handling the missing values appropriately, which I don't think you're doing now.

The code below creates character variables with dots. I'm not sure if you set missing to blank first it would be fixed or you can add a conditon to check for missing data.

DO;

if not missing(AMT) then amt1 = put(AMT ,10.);    /*  data type conversion */
if not missing(NBR) then NBR1 = put(NBR ,10.);

; END;

[SASAna]

I will try adding 'not mising' to all the reformatting of the fileds. Doe athat applies to 'INPUT'  - Charcter to Number conversion?

Is the below example looks fine?

DO;

if not missing(AMT) then amt1 = put(AMT ,10.);    /*  Number to charcter conversion*/
if not missing(NBR) then NBR1 = put(NBR ,10.);

if not missing(UNIT) then UNIT1 = input(UNIT,10.);   /* charcter to number conversion */

; END;

[ballardw · Accepted solution]

---

@SASAna wrote:

Hi Reeza,

IF "class" = "'A'" THEN

DO;

AMT1 = put(AMT ,10.);    /*  data type conversion */
NBR1 = put(NBR ,10.);

; END;
ELSE

DO;

AMT1= AMT;    /* for other classess data type conversion not needed */

NBR1 = NBR;

END;

---

Those statements create periods as text when missing values for AMT or NBR UNLESS you set OPTIONS MISSING=' '; before the data step that generates them.

[SASAna]

You are right . I added option missing =' ' before the datastep & before ODS report , all the dots vanished.
Thanks much,
Ana