I am trying to put zero where the values are missing in my Across Report and getting blank space(.) in the report output.SET NODATA can not be used because of Empty values are needed for two are more columns in my report.Is there any idea to put zero instead of blank space?

Example:
SET NODATA=' '
TABLE FILE CAR
SUM
COMPUTE SALES/I11M MISSING ON=IF SALES IS MISSING THEN 0 ELSE SALES;
ACROSS COUNTRY
BY CAR
END

in the above example the sales column should have zero for missing data and others need blank space for Missing data.

This message has been edited. Last edited by: FP Mod Chuck,

If you want 0 where data is missing then use
SET NODATA='0'
You have set it to blank hence you get a blank when data is missing.

I think OP is asking only the Sales column MISSING values needs to be '0' and other columns MISSING values should be blank. The SET NODATA='0' would take care of the other columns, I am also interested to see on how to set NODATA for a specific column differently.

In the below sample I have assigned 'X' as the default missing displayed data but I have set a different value for missing seats.

Retail_MIS exist only to illustrate a missing value

SET NODATA='X'
DEFINE FILE CAR
RETAIL_MIS/D7 MISSING ON=IF RETAIL_COST LE 10000 THEN MISSING ELSE RETAIL_COST; 
SEATS_MIS /I3 MISSING ON=IF SEATS LE 2 THEN MISSING ELSE SEATS; 
SEATS_NEW /I3 MISSING ON=IF SEATS_MIS IS MISSING THEN 123 ELSE SEATS_MIS;
END
TABLE FILE CAR
SUM RETAIL_COST
    RETAIL_MIS
    SEATS
    SEATS_MIS
    SEATS_NEW
ACROSS COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND';
END

If you look at the Triumph row you will see that its RETAIL_MIS is displayed with the default missing value and the SEATS_NEW with the overwrite assigned one (where SEATS_MIS as the default one).
So as final report, you should only print RETAIL_MIS and SEATS_NEW. The other are included to show the original value only BEFORE manipulation.

Try this code, it will help you to get desired output,

 SET NODATA=''
DEFINE FILE CAR
SALES/D7 MISSING ON=SALES;
COUNTRY/A10 =STRIP(10, COUNTRY, ' ', 'A10');
END
TABLE FILE CAR
SUM RETAIL_COST
   SALES
ACROSS COUNTRY
BY CAR
ON TABLE SET  ASNAME ON 
ON TABLE HOLD AS XYZ FORMAT ALPHA
END



DEFINE FILE XYZ
Rec_Type/A55 = 'ENGLAND';
D_ACROSS_SORT/I2 = 1;
END
-RUN

TABLE FILE XYZ
PRINT  RETENGLAND 
 SALENGLAND 
 CAR
 Rec_Type
 D_ACROSS_SORT
ON TABLE HOLD AS EN
END



DEFINE FILE XYZ
Rec_Type/A55 = 'FRANCE';
D_ACROSS_SORT/I2 = 2;
END
-RUN

TABLE FILE XYZ
PRINT   RETFRANCE 
 SALFRANCE
 CAR
 Rec_Type
 D_ACROSS_SORT
ON TABLE HOLD AS FR
END


DEFINE FILE XYZ
Rec_Type/A55 = 'ITALY';
D_ACROSS_SORT/I2 = 3;
END
-RUN

TABLE FILE XYZ
PRINT   RETITALY 
 SALITALY 
 CAR
 Rec_Type
 D_ACROSS_SORT
ON TABLE HOLD AS IT
END


TABLE FILE EN
SUM
     EN.EN.RETENGLAND/I11C AS 'Retail Cost'
     EN.EN.SALENGLAND/I11C AS 'Sales'
BY  EN.EN.CAR AS 'Car'
ACROSS D_ACROSS_SORT NOPRINT  AS ''
ACROSS Rec_Type AS 'Country'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,
	 ENDSTYLE
MORE
FILE FR
MORE
FILE IT
END 

Thanks for your reply Martin.It's working fine with less or greater than of sales condtions.But if i go with the following conditions it repeat only x for all columns

SET NODATA='X'
DEFINE FILE CAR
SALES_MIS /I3 MISSING ON=IF SALES EQ MISSING THEN 0 ELSE SALES;
SALES_NEW /I3 MISSING ON=IF SALES_MIS EQ MISSING THEN 0 ELSE SALES_MIS;
END
TABLE FILE CAR
SUM
SALES -* This column should provide blank space for missing values
SALES_NEW -* This column should provide zero for missing values
ACROSS COUNTRY
BY CAR
END

I got a final solution by simplifying the code of Chaudhary.Thanks Chaudhary.

SET NODATA=''
DEFINE FILE CAR
SALES/D7 MISSING ON=SALES;
END

TABLE FILE CAR
SUM
RETAIL_COST -* Zero for Missing Data
SALES -* Blank space for Missing Data
BY CAR
ACROSS COUNTRY
ON TABLE HOLD AS XYZ FORMAT ALPHA
END

TABLE FILE XYZ
PRINT
*
END

quote:

I got a final solution by simplifying the code of Chaudhary.Thanks Chaudhary.

quote:

SET NODATA=''
DEFINE FILE CAR
SALES/D7 MISSING ON=SALES;
END

TABLE FILE CAR
SUM
RETAIL_COST -* Zero for Missing Data
SALES -* Blank space for Missing Data
BY CAR
ACROSS COUNTRY
ON TABLE HOLD AS XYZ FORMAT ALPHA
END

TABLE FILE XYZ
PRINT
*
END

Durai, By using this you will not be getting the Across column(country) header .