SAS Variable formatted as hex4. sometimes gets output in exponential notation

[lchristensen]

Creating pairs for z/OS device addresses for replication. The device addresses are a four digit HEX value: ie, 8E00 - 8E0F would be 16 addresses.

When the variable Tgt_Address is output those values where the last digit of the hex string is a number between 0 and 9 (inclusive) the value is shown in exponential notation. When the last digit is alpha (A-F) the value is properly shown.

Code segment below:

/* ---------------------------------------------------------------------------------------------------------------------------------------- */
/* Build pairs for the TPF volumes. */
/* ---------------------------------------------------------------------------------------------------------------------------------------- */
data Work.Build_TPF;
file MOVETPF;
format tpfa hex4.;
format Tgt_Address hex4.;
if _N_ = 1 then
Put @ 001 'Unit_Address' + (0) ','
+ (0) 'Tgt_Serial_Number' + (0) ','
+ (0) 'Tgt_Address' + (0) ','
+ (0) 'Tgt_Volser' + (0) ','
;
retain Tgt_Address 8E00x;
do tpfa = 7800x to 787Fx;
Tgt_Volser = 'XX' || put(Tgt_Address,hex4.);
Tgt_Serial_Number = 50344;
put @ 001 tpfa + (0) ','
+ (0) Tgt_Serial_Number + (0) ','
+ (0) Tgt_Address  + (0) ','
+ (0) Tgt_Volser + (0) ','
;
Tgt_Address = Tgt_Address + 1;
end;
do tpfb = 7900x to 797Fx;
Tgt_Volser = 'XX' || put(Tgt_Address,hex4.);
Tgt_Serial_Number = 50344;
put @ 001 tpfa + (0) ','
+ (0) Tgt_Serial_Number + (0) ','
+ (0) Tgt_Address  + (0) ','
+ (0) Tgt_Volser + (0) ','
; ;
Tgt_Address = Tgt_Address + 1;
end;
run;

[ballardw · Accepted solution]

Is that CSV by any chance made by exporting to Excel and converting to CSV? Or opened in Excel at any point?

The culprit may well be Excel if that is the case.

When I put values to the log, for example:

821  data test;
822     retain Tgt_Address 8E00x;
823     do tpfa = 7800x to 787Fx;
824        put tgt_address= hex4.  ;
825        Tgt_Address = Tgt_Address + 1;
826        output;
827     end;
828     format tgt_address hex4.;
829  run;

Tgt_Address=8E00
Tgt_Address=8E01
Tgt_Address=8E02
Tgt_Address=8E03
Tgt_Address=8E04
Tgt_Address=8E05
Tgt_Address=8E06
Tgt_Address=8E07
Tgt_Address=8E08
Tgt_Address=8E09
Tgt_Address=8E0A
Tgt_Address=8E0B
Tgt_Address=8E0C
Tgt_Address=8E0D
Tgt_Address=8E0E
Tgt_Address=8E0F
Tgt_Address=8E10
Tgt_Address=8E11
Tgt_Address=8E12
Tgt_Address=8E13
Tgt_Address=8E14
Tgt_Address=8E15
Tgt_Address=8E16
Tgt_Address=8E17
Tgt_Address=8E18
Tgt_Address=8E19
Tgt_Address=8E1A
Tgt_Address=8E1B
Tgt_Address=8E1C
Tgt_Address=8E1D
Tgt_Address=8E1E
Tgt_Address=8E1F
Tgt_Address=8E20
Tgt_Address=8E21
Tgt_Address=8E22
Tgt_Address=8E23
Tgt_Address=8E24
Tgt_Address=8E25
Tgt_Address=8E26
Tgt_Address=8E27
Tgt_Address=8E28
Tgt_Address=8E29
Tgt_Address=8E2A
Tgt_Address=8E2B
Tgt_Address=8E2C
Tgt_Address=8E2D
Tgt_Address=8E2E
Tgt_Address=8E2F
Tgt_Address=8E30
Tgt_Address=8E31
Tgt_Address=8E32
Tgt_Address=8E33
Tgt_Address=8E34
Tgt_Address=8E35
Tgt_Address=8E36
Tgt_Address=8E37
Tgt_Address=8E38
Tgt_Address=8E39
Tgt_Address=8E3A
Tgt_Address=8E3B
Tgt_Address=8E3C
Tgt_Address=8E3D
Tgt_Address=8E3E
Tgt_Address=8E3F
Tgt_Address=8E40
Tgt_Address=8E41
Tgt_Address=8E42
Tgt_Address=8E43
Tgt_Address=8E44
Tgt_Address=8E45
Tgt_Address=8E46
Tgt_Address=8E47
Tgt_Address=8E48
Tgt_Address=8E49
Tgt_Address=8E4A
Tgt_Address=8E4B
Tgt_Address=8E4C
Tgt_Address=8E4D
Tgt_Address=8E4E
Tgt_Address=8E4F
Tgt_Address=8E50
Tgt_Address=8E51
Tgt_Address=8E52
Tgt_Address=8E53
Tgt_Address=8E54
Tgt_Address=8E55
Tgt_Address=8E56
Tgt_Address=8E57
Tgt_Address=8E58
Tgt_Address=8E59
Tgt_Address=8E5A
Tgt_Address=8E5B
Tgt_Address=8E5C
Tgt_Address=8E5D
Tgt_Address=8E5E
Tgt_Address=8E5F
Tgt_Address=8E60
Tgt_Address=8E61
Tgt_Address=8E62
Tgt_Address=8E63
Tgt_Address=8E64
Tgt_Address=8E65
Tgt_Address=8E66
Tgt_Address=8E67
Tgt_Address=8E68
Tgt_Address=8E69
Tgt_Address=8E6A
Tgt_Address=8E6B
Tgt_Address=8E6C
Tgt_Address=8E6D
Tgt_Address=8E6E
Tgt_Address=8E6F
Tgt_Address=8E70
Tgt_Address=8E71
Tgt_Address=8E72
Tgt_Address=8E73
Tgt_Address=8E74
Tgt_Address=8E75
Tgt_Address=8E76
Tgt_Address=8E77
Tgt_Address=8E78
Tgt_Address=8E79
Tgt_Address=8E7A
Tgt_Address=8E7B
Tgt_Address=8E7C
Tgt_Address=8E7D
Tgt_Address=8E7E
Tgt_Address=8E7F

[lchristensen]

Yes. The output to .csv file results in being opened by Excel and apparently Excel doesn't know what HEX is, though I've read there are functions to switch from hex to decimal and back.

I may have to resort to putting quotes around the hex variable values and treating as text.

Thank you.

[ballardw]

---

@lchristensen wrote:

Yes. The output to .csv file results in being opened by Excel and apparently Excel doesn't know what HEX is, though I've read there are functions to switch from hex to decimal and back.

I may have to resort to putting quotes around the hex variable values and treating as text.

Thank you.

---

Actually Excel doesn't know what any input is. It applies rules to each individual value because spreadsheets do not have the concept of "variable" with the same properties for all values. So depending on the ranges of values it treated some of the values as exponential.

If you check closely in the Excel cell properties you would likely find that values like "8E0A" are actually character.

One of the slightly advanced bits with data interchange is make your CSV directly from SAS, there are several ways such as Proc Export, and then do not open the file in Excel. Or if you must make sure that you do not save the file after opening in Excel. Other "nice" things that Excel will do is turn unquoted text values like 5-8 in a CSV file to a number representing the current year value of May 8 (possibly August 5 depending on system settings). Nothing like finding "dates" in the middle of a list of part numbers.

The same comment applies to Tab delimited files if you have Excel associated with an extension that matches your file name.