Proc export .csv with leading zeros

[Babloo]

I've used the following code to export my dataset to .csv. However I could not see leading zeros for one of the variable 'zip'?

proc export data=country outfile="/data/country.csv" dbms=csv replace;

run;

What is the better way to output .csv with leading zeros?

I ran this code in EG 7.1 with SAS version 9.3.

[Kurt_Bremser]

Have you taken a look at the csv file itself?

If a column is numeric, assign it a Zw. format so that leading zeroes are printed in the .csv.

Anyway, if you see leading zeroes in the .csv file, the problem lies with the software used to read the .csv.

[Babloo]

'Zip' variable in my dataset is character not numeric.

[Steelers_In_DC]

Understood, are you opening this file in excel, or another similar database application?  If so it's that application that is dropping the character format.  Try opening it in notepad and I suspect that you will see the leading zeros as intended.

[Babloo]

Yes, I opening it in excel. So don't we've any other better ways to view the leading zeros in excel?

[Steelers_In_DC]

Here is a piece of ods code that I use frequently, it has a few different formats that you can see:

ods _all_ close;

ods tagsets.ExcelXP path='/export/home/pp78499/sasuser.v92' file='QC13_OUTPUT.xml'

style=printer;

ods tagsets.ExcelXP options(sheet_name='SUM_AT_FACILITY' AUTOFILTER = 'ALL'); /* add sheet name and filter*/

proc print data=SUM_AT_FACILITY noobs;

var power_id facility_id / style(column)={tagattr='Format:@'}; /* format as character for leading zeros */

var ead_date_end sum_trans / style={tagattr='Format:#,###'};run;quit; /* format comma */

ods tagsets.ExcelXP options(sheet_name='BCR_TRANSACTIONS' AUTOFILTER = 'ALL');

proc print data=BCR_TRANSACTIONS noobs;

var power_id facility_id acct_full_acct_id / style(column)={tagattr='Format:@'};

var _all_ ;run;quit;

ods tagsets.ExcelXP options(sheet_name='WBDW_DATE' AUTOFILTER = 'ALL');

proc print data=WBDW_DATE noobs;

var power_id facility_id acct_full_acct_id / style(column)={tagattr='Format:@'};

var _all_ ;run;quit;

ods tagsets.ExcelXP options(sheet_name='LGD_ABS' AUTOFILTER = 'ALL');

proc print data=LGD_ABS noobs;

var power_id facility_id acct_full_acct_id / style(column)={tagattr='Format:@'};

var _all_ ;run;quit;

ods tagsets.ExcelXP close;

[Steelers_In_DC]

I agree with Kurt, open the csv in a notepad and the leading zeros are probably there.  If this is being read into another software (tableau) you'll have to check that they are pulling the leading zeros with the correct informat.  If you are opening it into excel the format will not be saved, a work around is to copy and paste it into excel if that's where you need it.

[Cynthia_sas]

Hi:

If you are opening the CSV file with Excel, then using PROC EXPORT will not cause Excel to "respect" the SAS format. Excel has a default way that it treats numbers and leading zeros are ignored when you have a CSV file or other file. You need to send a Microsoft format from SAS to Excel using the Output Delivery System. This paper https://support.sas.com/resources/papers/proceedings11/266-2011.pdf shows the 2 methods: HTMLSTYLE override or TAGATTR override. The one you choose will correspond to the ODS destination (HTML or XML) you use. Leading zeroes are shown on page 6-7.

cynthia

[Babloo]

Document which you suggested deals with leading zeros for numeric variables.However, I was looking for leading zeros with character variables.

[Cynthia_sas · Accepted solution]

My document does show a way to deal with leading zeroes as character variables, you would follow the example shown for the ISBN number example in the beginning of the paper. ISBN does not have leading zeroes, but does show how to specify that Excel treat a number as text. A value like 00023 will still be treated by Excel as a number and until you specify that Excel should treat the number as a character string, it will not be handled correctly. See #1 output below:.

Here's the code that produced the above screen shot.

Cynthia

** in this data, X is a character variable;

** and Y is a numeric variable;

data testit;

  x='00023';

  y=00098;

  format y z5.;

run;

ods msoffice2k file='c:\temp\num_as_char.xls' style=htmlblue;

proc print data=testit;

title '1) default Excel Treatment';

  var x y;

run;

proc print data=testit;

title '2) With Style Override';

  var x / style(column)={htmlstyle='mso-number-format:\@'};

  var y / style(column)={htmlstyle='mso-number-format:00000'};

run;

ods msoffice2k close;

[Babloo]

Thanks for your code. However, I had more than 1 lakh records with leading zeros. So I tried the following code instead of using proc print. However I could not get leading zeros displayed in .xls. May I request you to tell me what is wrong in this code?

data testit;

  x='00023';

  y=00098;

  format y z5.;

run;

ods msoffice2k file='/data/num_as_char.xls' style=htmlblue;

proc report data=testit nowd;

title '3b) TAGATTR Style Override';

column x y;

  define x / display

style(column)={tagattr='Format:@'};

run;

  ods msoffice2k close;

On a related point I wish to know to create .csv with leading zeros for the same data.

[Ksharp]

Add a character before it to make CSV take it as a character :

data testit;

  x='00023';_x=cats(x,'09'x);

  y=00098;

  format y z5.;

run;

[Babloo]

Thank you!

What is '09'x in your cats function?

[Ksharp]

A TAB character to make sure EXCEL or CSV take it as a character variable ,not numeric variable .

[Cynthia_sas]

Hi:

  One problem with the code you've posted is that when you use ODS MSOFFICE2K, that is an HTML-based destination and you MUST use HTMLSTYLE= as the override, as explained in my paper and shown in my posted code. To use TAGATTR, you have to override the format with TAGSETS.EXCELXP.

cynthia