DATA Step, Macro, Functions and more

Proc report ODS excel output to remove dots

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Proc report ODS excel output to remove dots

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

 

 

 


Accepted Solutions
Solution
‎02-08-2017 07:07 PM
Super User
Posts: 10,500

Re: Proc report ODS excel output to remove dots


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.

View solution in original post


All Replies
PROC Star
Posts: 288

Re: Proc report ODS excel output to remove dots

Have you tried this?

 

options  missing = '';

 

 

PROC Star
Posts: 288

Re: Proc report ODS excel output to remove dots

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

Frequent Contributor
Posts: 84

Re: Proc report ODS excel output to remove dots

Yes, Colline, you are right . They could be missing Numbers.
Super User
Posts: 17,824

Re: Proc report ODS excel output to remove dots

Try 

 

option missing = ' ';

 

Frequent Contributor
Posts: 84

Re: Proc report ODS excel output to remove dots

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

Super User
Posts: 17,824

Re: Proc report ODS excel output to remove dots

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;

Frequent Contributor
Posts: 84

Re: Proc report ODS excel output to remove dots

 

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;

Solution
‎02-08-2017 07:07 PM
Super User
Posts: 10,500

Re: Proc report ODS excel output to remove dots


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.

Frequent Contributor
Posts: 84

Re: Proc report ODS excel output to remove dots

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

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 170 views
  • 2 likes
  • 4 in conversation