Help using Base SAS procedures

Proc export .csv with leading zeros

Accepted Solution Solved
Reply
Super Contributor
Posts: 426
Accepted Solution

Proc export .csv with leading zeros

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.


Accepted Solutions
Solution
‎05-13-2015 05:45 PM
SAS Super FREQ
Posts: 8,743

Re: Proc export .csv with leading zeros

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:.

show_style_override.png

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;

View solution in original post


All Replies
Super User
Posts: 6,936

Re: Proc export .csv with leading zeros

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 426

Re: Proc export .csv with leading zeros

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

Valued Guide
Posts: 858

Re: Proc export .csv with leading zeros

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.

Super Contributor
Posts: 426

Re: Proc export .csv with leading zeros

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

Valued Guide
Posts: 858

Re: Proc export .csv with leading zeros

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;

Valued Guide
Posts: 858

Re: Proc export .csv with leading zeros

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.

SAS Super FREQ
Posts: 8,743

Re: Proc export .csv with leading zeros

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

Super Contributor
Posts: 426

Re: Proc export .csv with leading zeros

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

Solution
‎05-13-2015 05:45 PM
SAS Super FREQ
Posts: 8,743

Re: Proc export .csv with leading zeros

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:.

show_style_override.png

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;

Super Contributor
Posts: 426

Re: Proc export .csv with leading zeros

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.

Super User
Posts: 9,681

Re: Proc export .csv with leading zeros

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;

Super Contributor
Posts: 426

Re: Proc export .csv with leading zeros

Thank you!

What is '09'x in your cats function?

Super User
Posts: 9,681

Re: Proc export .csv with leading zeros

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

SAS Super FREQ
Posts: 8,743

Re: Proc export .csv with leading zeros

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 17 replies
  • 4323 views
  • 7 likes
  • 5 in conversation