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
@SASAna wrote:
Hi Reeza,
IF "class" = "'A'" THEN
DO;
AMT1 = put(AMT ,10.); /* data type conversion */
NBR1 = put(NBR ,10.);; END;
ELSEDO;
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.
Have you tried this?
options missing = '';
I'm assuming your dots are missing values for numeric variables. If not, can you give an example?
Try
option missing = ' ';
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
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;
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;
@SASAna wrote:
Hi Reeza,
IF "class" = "'A'" THEN
DO;
AMT1 = put(AMT ,10.); /* data type conversion */
NBR1 = put(NBR ,10.);; END;
ELSEDO;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.