The SAS Output Delivery System and reporting techniques

Character format with TAGSET EXCELXP

Reply
Contributor
Posts: 40

Character format with TAGSET EXCELXP

Hello,

I am experiencing a little problem with an export !

I am using tagset excelxp but I have two problems :

1.) I have a character variable with values (ID) such as : 02578 03659 08753

and when it exports to Excel, it cancels the 0 so that my variable becomes 2578 3659 8753

Is there an option to prevent that ? Or something else to do ?

2) my second problem is about missing values of numeric variables. How could you to suppress the . in excel ? I need to have blanks for numeric missing values in EXCEL but it keeps the . of sas missing values.

Thnaks a lot

SAS Super FREQ
Posts: 8,740

Re: Character format with TAGSET EXCELXP

Hi:

  #1 -- there have been many previous forum postings about this. Generally when Excel drops a leading 0 you have to use the TAGATTR style override (with TAGSETS.EXCELXP) to tell Excel to use a Microsoft format that keeps the leading zero or else you have to tell Excel that the field is a character string. I have an example of this in my paper: http://support.sas.com/resources/papers/proceedings11/266-2011.pdf see pages 2, 3, 4 for the "leading zero" example.

  #2 -- the way to turn off the display of dot (.) for missing is to use a SAS system option:

options missing=' ';  (quote-space-quote) This tells SAS to DISPLAY the normal missing value of dot as space. And, then, of course, you would want to set it back to dot after your step was finished:

options missing='.';

cynthia

Ask a Question
Discussion stats
  • 1 reply
  • 494 views
  • 0 likes
  • 2 in conversation