BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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

17 REPLIES 17
Kurt_Bremser
Super User

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
Rhodochrosite | Level 12

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

Steelers_In_DC
Barite | Level 11

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
Rhodochrosite | Level 12

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
Barite | Level 11

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
Barite | Level 11

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
SAS Super FREQ

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
Rhodochrosite | Level 12

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

Cynthia_sas
SAS Super FREQ

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;

Babloo
Rhodochrosite | Level 12

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
Super User

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
Rhodochrosite | Level 12

Thank you!

What is '09'x in your cats function?

Ksharp
Super User

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

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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