topic Re: Proc report ODS excel output to remove dots in SAS Programming

Proc report ODS excel output to remove dots

SASAna — Wed, 08 Feb 2017 22:49:20 GMT

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

Re: Proc report ODS excel output to remove dots

collinelliot — Wed, 08 Feb 2017 23:04:45 GMT

Have you tried this?

options missing = '';

Re: Proc report ODS excel output to remove dots

Reeza — Wed, 08 Feb 2017 23:04:57 GMT

Try

option missing = ' ';

Re: Proc report ODS excel output to remove dots

collinelliot — Wed, 08 Feb 2017 23:05:27 GMT

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

Re: Proc report ODS excel output to remove dots

SASAna — Wed, 08 Feb 2017 23:27:31 GMT

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

Re: Proc report ODS excel output to remove dots

SASAna — Wed, 08 Feb 2017 23:28:51 GMT

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

Re: Proc report ODS excel output to remove dots

Reeza — Wed, 08 Feb 2017 23:36:32 GMT

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;

Re: Proc report ODS excel output to remove dots

ballardw — Wed, 08 Feb 2017 23:44:53 GMT

@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.

Re: Proc report ODS excel output to remove dots

SASAna — Wed, 08 Feb 2017 23:45:18 GMT

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;

Re: Proc report ODS excel output to remove dots

SASAna — Thu, 09 Feb 2017 00:08:28 GMT

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