Your SAS programs, embedded in web apps and elsewhere

preceding zeros missing in excel in numeric as well as character value

Reply
Occasional Contributor
Posts: 10

preceding zeros missing in excel in numeric as well as character value

Hi,

 

I have made sas stored procedure and then transfered output to excel. But in excel preceding zero's are missing.

 

e.g.   value in sas                 value in excel

           005407                         5407

           001                               1

 

i have tried following code.

 

DEFINE SERIES_BASE / DISPLAY "SERIES BASE" Style(data)={tagattr='Format: @'} FORMAT= $CHAR4.;

 

DEFINE SERIES_BASE / DISPLAY "SERIES BASE" Style(data)={tagattr='Format: text'} FORMAT= $CHAR4.;

 

DEFINE SERIES_BASE / DISPLAY "SERIES BASE" Style(data)={tagattr='Format: $'} FORMAT= $CHAR4.;

 

I am getting error.

 

Can anyone please help.

 

Thanks in advance

 

Super User
Posts: 6,962

Re: preceding zeros missing in excel in numeric as well as character value

Excel interprets any data cell that contains only digits as a number and formats accordingly. That's just Excel.

You might try adding a single quote before the leading zeroes.

For reference, please post your ODS statement(s) you used for the proc report.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,413

Re: preceding zeros missing in excel in numeric as well as character value

Hi,

 

Your define should be - and do note whilst we are on the subject, follow some good coding practices - i.e. not all upper case or mixed case, use indentation etc. and there is a code window {i} above where you post for code.

define series_base / display "SERIES BASE" style={tagattr='Format:@'};

If that still persists you will need to post example test data in the form of a datastep and the code (formatted so it is readable) so we can have a look.

Ask a Question
Discussion stats
  • 2 replies
  • 132 views
  • 1 like
  • 3 in conversation