topic Re: ods tagsets.ExcelXp-Display null values without dot in SAS Programming

ods tagsets.ExcelXp-Display null values without dot

Ronein — Wed, 27 Nov 2019 08:29:40 GMT

Hello

I use ods tagsets.ExcelXp to export sas data set to XML sheet .

The null values appear with dot (.) symbol in the XML file.

What is the way to replace dot values(.) with empty values?

data ttbl;
input ID group $ Y;
cards;
1 a .
2 a .
3 a 30
4 a 40
5 b .
6 b 25
7 b 35.85
8 c  50
9 c 60
10 c .
;
run;




ods _all_ close;/*To prevent print on screen*/
ods tagsets.ExcelXp
File ='Ex3b.xml'
path="/My path/";
proc print data=ttbl noobs label; 
run; 
ods tagsets.ExcelXp close;
ods listing;

Re: ods tagsets.ExcelXp-Display null values without dot

Ronein — Wed, 27 Nov 2019 08:37:37 GMT

Okay, I found the answer. Need to use options missing=' ' statement.

Options missing = ' '; 
ods _all_ close;/*To prevent print on screen*/
ods tagsets.ExcelXp
File ='Ex3c.xml'
path="/My path/";
proc print data=ttbl noobs label; 
run; 
ods tagsets.ExcelXp close;
ods listing;
Option missing=.;/*go back to the default*/

Re: ods tagsets.ExcelXp-Display null values without dot

ballardw — Wed, 27 Nov 2019 15:47:41 GMT

Since you are using Proc Print you could also use formats for each variable that display space instead of . for missing values, or even "NULL" if desired.

Re: ods tagsets.ExcelXp-Display null values without dot

Tom — Wed, 27 Nov 2019 15:58:10 GMT

Why are you using PROC PRINT if you don't want a print out?

libname out xlsx "/My path/Ex3b.xlsx";
proc copy inlib=work outlib=out;
  select ttbl;
run;