Keeping leading zeroes when exporting in sas

[Q1983]

IF CO_VA_DFLT_RSN_CD = 'DOB' THEN REASON_FOR_DEFAULT = '001';

         ELSE IF CO_VA_DFLT_RSN_CD =  ' '  THEN REASON_FOR_DEFAULT = ' ';

         ELSE IF CO_VA_DFLT_RSN_CD = 'ILB' THEN REASON_FOR_DEFAULT = '002';

         ELSE IF CO_VA_DFLT_RSN_CD = 'ILF' THEN REASON_FOR_DEFAULT = '003';

This is a snippet of the output statement

REASON_FOR_DEFAULT End As REASON_FOR_DEFAULT format $3.,/*which means this is supposed maintain the leading zero*/

This file runs and saves as a .csv file.  I have been reading alot on how leading zeroes are ommitted from csv files when opening in csv or excel.  Is there a way that sas can freeze the leading zero format on the sas side to maintain the leading zero.  The above example is part of the code

So for example reason_for_default = 002 comes over as 2.

This is the output to csv statement

%Let route = %str(/D5/dors09/dors/sascode/reporting/);

%Let outputfile = %str(test_&new_dt_key..csv);

%Let pth = &route&outputfile;

%Put Output File &pth;

[SASKiwi]

This is not really a SAS issue. To prove that open your CSV in a text editor like Notepad and you will see the leading zeros.

If you double-click on the CSV file to open it in Excel, it is Excel that is assuming that your column is numeric and is dropping the leading zeros.

An easy way to avoid this behaviour would be to add a character on the front of your column: REASON_FOR_DEFAULT = 'R001';

If you want to stick with 001 you could do an Excel Data Import and specify the the column REASON_FOR_DEFAULT as text instead of double-clicking on it and that will retain the leading zeroes.

[Q1983]

With the first option does that not mean that there would be an R in front of the 001?

Also I did the second option and it works when I open it.  However when I save it as a csv then open it again I get the same problem.  The leading zero dissapear

[SASKiwi]

CSV's don't store Excel column formating so each time you import the data you have to go through the same process. If you stored the data as a spreadsheet (.XLS or .XLSX) then it would work correctly.

My first suggestion of putting an R in front of course means your codes change but it forces Excel to read it in as character. You could also try REASON_FOR_DEFAULT = '''001';  <== Add two single quotes before your code (3 in total). SAS should write this out into the CSV as just one single quote which Excel should then read in as character without doing a data import.

[Patrick]

As says this is a Excel/.csv limitation. If this is about providing users data to be opened via Excel then consider creating Excel or XML format instead using one of the methods as outlined in below link:

How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

If there are reasons why you can't create a real Excel then one way to go would be doing something like in the code below.

proc format;

  picture ptest (default=8)

    other='99999"' (prefix='="')

  ;

run;

data test;

  do counter=10 to 1000 by 99;

    value=counter;

    output;

  end;

  format value ptest.;

run;

proc export data=test

  outfile='c:\temp\test.csv'

  dbms=csv

  replace;

run;

[Q1983]

Your suggestion to place double quotes in front of the string worked however it produces a single quote like this

'013 instead of 013.  I tried a substr function to begin a 2 to address that however it goes back to removing leading zeros.  Any ideas

[Tom]

A CSV file has no place to store any information on the variable types. If you insist on storing your data in CSV files and trying to open them with Excel you will not be able to preserve the leading zeros.  Either store the data in a format that can store the metadata about the variable type (a SAS dataset might be a good way) or do not use Excel to try to manipulate the CSV files.  Otherwise you will need to adopt one of the strategies to modify the values stored in the CSV file so that Excel will treat the value as a character string instead of a number.