Strings ending with 'E00..' read as numbers

[Jan_S]

Hi,

I m trying to create an excel from mainframe dataset using sas. The strings in the file (ending with 'E followed by zeros') are being treated as numbers by sas. How to override this? I have given the input declaration as $char. But that does not control reading my input as characters.Eg: '075E00' is getting written as 75 in the output excel.  Can some one help ?

Thanks,

Janet

[ballardw]

Some code might let us diagnose better.

If the value in SAS is a string and Excel thinks it is a number I tend to think Excel is the culprit.

Where is the "input declaration as $char." occuring? Reading the data before export to Excel or on attempting to read the created Excel sheet back into SAS to compare?

How are you writing to Excel? How are you reading Excel (if doing so)?

[Jan_S]

The value is being treated as number instead of string during SAS read. Input declaration:

OPTIONS nofmterr;              

DATA INREPT1;                  

INFILE I1IN;                  

informat DCN $char13.         

          GROUP $char10.       

          CERT $char9.         

          HCID $char20.        

          MC $char2.           

          MEMNAME $char39.     

          NATIND $char7.       

          CASENUM $char10.     

          ACTCDE $char6.       

          CMPY $char4.         

          MBU $char8.          

          CONTRENDT $char10.   

          CONTEFFDT $char10.   

          ;                   

INPUT @1 DCN $char13.         

      @15 GROUP $char10.      

      @26 CERT $char9.        

      @36 HCID $char20.       

      @57 MC $char2.          

      @60 MEMNAME $char39.    

      @100 NATIND $char7.     

      @108 CASENUM $char10.   

      @119 ACTCDE $char6.     

      @126 CMPY $char4.       

      @131 MBU $char8.        

      @140 CONTRENDT $char10. 

      @151 CONTEFFDT $char10. 

;                            

I have  read my mainframe dataset as character explicitly using $char. But SAS still accepts the character '075E00' as number. Per my reqt, the field GROUP in the above listed columns can take any either string/numeric values. So i ve given the below in my PROC PRINT. Without specifying the format '000000' for group field, the leading zeros get truncated.

PROC PRINT DATA=INREPT1 NOOBS;                                       

VAR DCN / style={tagattr='format:####'                               

      htmlstyle="mso-number-format:'\@'"};                           

var GROUP/ style={tagattr='format:000000'                           

      htmlstyle="mso-number-format:'\@'"};                           

---

run;

Is it the format which is causing the data declared as ($char)  to be read numbers?

[Astounding]

That's easy enough to check.  But the first place I would look is the incoming data.  Does your INFILE source contain "75", or does it contain "075E00"?

If the INFILE source actually contains "075E00" then try an unformatted inspection of GROUP:

proc freq data=INREPT1;

tables group / missing;

run;

That should at least prove whether or not the format is causing the problem.

[Tom]

SAS is not the issue here. It is Excel that is converting the number.  It does this normally by default when you type into a cell or import a CSV file.  You can override it manually with the import or text to columns wizard in Excel by telling it the column is text.

But in your code above you are explicitly telling Excel to treat the value of GROUP as a number by using tagattr='format:000000' .

I think that you want tagattr='text'.

[Jan_S]

As you said SAS is not the issue. While it is being written to excel, the format through tagattr does not reflect in excel columns,however dynamically, the data gets formatted (character '075E00' gets formatted as 000075)

We are trying to attach a mianframe dataset information in .XLS format and sending a mail to some third party users. We are achcieving this conversion of PS file to XLS file through SAS utility by using ODS tagsets excelxp statements via XML fomratting. While writing the XML code(to generate xls), the alphanumeric data with value 'E' inbetween numbers are treated as numeric and truncation happens. For example if the data is 075E00 in mainframe file, the excel is displaying it as 000075(i.e 75 * 10^0 = 75) which is scientific representation of numbers. We need to report the real data and we dont need such truncation to happen. Tried with lots of formatting overrides available with ODS tagsets commands but nothing worked. Based on the value, the alphanumeric column is interpreting its own data type in the XML code.

Code chunk:

PROC PRINT DATA=INREPT1 noobs;  

VAR GROUP/ style={tagattr='type:String/text format:####' 

htmlstyle="mso-number-format:'\@'"};

Even with this specification, the numeric type override happens for this particular input. Kindly suggest any other approach to overcome this. Thanks!

[Astounding]

Janet,

I'm not whether an easy solution exists, but the problem is that these are legitimate numbers in SAS.  To SAS this means 75 times 10 (raised to the 0 power).  Similarly, this would be interpreted as 750:

075E01

Perhaps you could fudge the data.  Change it to H075E00, transfer the data, then remove the "H".

Good luck.