Zeroes getting omitted

[ssaha]

Hi SAS Experts

In a report which is in csv format, there is column say 'X'. So if one of the value say 0879 which is in the actual source data but in the report which is in csv format is showing as 879. But we want the value as 0879. 

So in the below mentioned sas code, we did the following by changing the numeric to string format but in this also the 0 is getting omitted.

For Example:

data test ;
prodID = 0457 ;
result = put(prodID , $4.) ;
run ;

proc print ;
run ;

So how can we resolve this so that when we download the report, it will show exact value as shown in the source data when the value starts with 0 in Excel.

[KachiM]

try:

result = put(prodID , $z4.) ;

[ssaha]

Thanks..It is working. But say if the length of the value is dynamic. Say one value consist of 2 digits say 05 and another value consist of 4 digits say 0567.And both the values starts with 0. So if we give $z4....for first value it will become 0004 which will append two extra zeroes that we don't want(As we want the exact value) and for second one it will remain as it is.

So how can we resolve this?

[Kurt_Bremser]

Keep strings as such. Store them in character variables.

If you export directly to xlsx, Excel will get it. When it has to read from csv, it won't.

[KachiM]

Your number 0567 is treated as a number which means a 3-digit number. Here is a way to dynamically formatting numbers as you wish.

[1] Create a Format from the number, of  non-zero digits, with the desired Zw. label.

[2] Use PUTN() function to get the zero-prefixed string.

proc format;
   value zfmt 1="z2."
              2="z3."
              3="z4."
              4="z5."
              5="z6.";
run;

Then a Data set with length of non-zero digits and the number.

data have;
input leng num;
datalines;
2 23
1 7
4 1234
5 54321
;
run;

Then Use PUTN() function:

data want;
   set have;
   strfmt = put(leng, zfmt.);
   str = putn(num, strfmt);
drop strfmt;
run;

You can adapt this for your application.

[Tom]

---

@ssaha wrote:

Hi SAS Experts

In a report which is in csv format, there is column say 'X'. So if one of the value say 0879 which is in the actual source data but in the report which is in csv format is showing as 879. But we want the value as 0879. 

So in the below mentioned sas code, we did the following by changing the numeric to string format but in this also the 0 is getting omitted.

For Example:

data test ;
prodID = 0457 ;
result = put(prodID , $4.) ;
run ;

proc print ;
run ;

So how can we resolve this so that when we download the report, it will show exact value as shown in the source data when the value starts with 0 in Excel.

---

Your program does not make any sense.  You create PRODID as a number and then try to apply a character format do it.

I think that SAS will notice the mistake and just use the 4. format instead of the $4. format that you coded.

If you want to convert a number to a digit string with leading zero then use the Z format instead.

Excel will NOT convert your character variables to numbers if you create an Excel workbook, unless you edit the cell value in Excel.  However if you produce a CSV file and let Excel open it without giving it information on how to treat each column it will convert cells that look like numbers into numbers. And worse.

[ballardw]

---

@ssaha wrote:

Hi SAS Experts

In a report which is in csv format, there is column say 'X'. So if one of the value say 0879 which is in the actual source data but in the report which is in csv format is showing as 879. But we want the value as 0879. 

So in the below mentioned sas code, we did the following by changing the numeric to string format but in this also the 0 is getting omitted.

For Example:

data test ;
prodID = 0457 ;
result = put(prodID , $4.) ;
run ;

proc print ;
run ;

So how can we resolve this so that when we download the report, it will show exact value as shown in the source data when the value starts with 0 in Excel.

---

How did you bring the CSV file into a SAS data set?

Proc Import makes guesses and if a column contains things that might be considered numeric it will do so and leading zeroes make no sense for integer values. If you need the value to have leading zeroes then you want a character variable and should modify you approach to reading the data. If you used Proc Import it created data step code that you could copy from the log and paste into the editor. Change the INFORMAT statement for the variable from a likely value like 32. to $10. or similar to read 10 characters. Change the Format statement to match or delete it.

If you already were using a data step to read the file then modify it.

And has been mentioned: Do not let a spreadsheet program open the file, edit and save the results as the spreadsheet software will do things like replace leading zero character values with numeric and saving them will completely change your file.