topic Re: Proc export .csv with leading zeros in SAS Procedures

Proc export .csv with leading zeros

Babloo — Wed, 13 May 2015 12:20:34 GMT

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.

Re: Proc export .csv with leading zeros

Kurt_Bremser — Wed, 13 May 2015 12:34:31 GMT

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.

Re: Proc export .csv with leading zeros

Steelers_In_DC — Wed, 13 May 2015 12:46:13 GMT

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.

Re: Proc export .csv with leading zeros

Babloo — Wed, 13 May 2015 13:16:00 GMT

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

Re: Proc export .csv with leading zeros

Steelers_In_DC — Wed, 13 May 2015 13:31:03 GMT

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.

Re: Proc export .csv with leading zeros

Babloo — Wed, 13 May 2015 14:04:32 GMT

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

Re: Proc export .csv with leading zeros

Cynthia_sas — Wed, 13 May 2015 14:07:57 GMT

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

Re: Proc export .csv with leading zeros

Ksharp — Wed, 13 May 2015 14:13:02 GMT

OR try ODS.

ods csv file='c:\temp\x.csv' ;

proc print data=x noobs;run;

ods csv close;

Re: Proc export .csv with leading zeros

Steelers_In_DC — Wed, 13 May 2015 14:18:46 GMT

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;

Re: Proc export .csv with leading zeros

Babloo — Wed, 13 May 2015 14:34:12 GMT

It is not working.

Re: Proc export .csv with leading zeros

Babloo — Wed, 13 May 2015 14:36:06 GMT

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

Re: Proc export .csv with leading zeros

Cynthia_sas — Wed, 13 May 2015 21:45:55 GMT

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;

Re: Proc export .csv with leading zeros

Ksharp — Thu, 14 May 2015 12:35:01 GMT

Another workaround.

data testit;

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

y=00098;

format y z5.;

run;

proc export data=testit outfile="c:\temp\country.csv" dbms=csv replace;

run;

Xia Keshan

Re: Proc export .csv with leading zeros

Babloo — Thu, 14 May 2015 14:09:02 GMT

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.

Re: Proc export .csv with leading zeros

Ksharp — Thu, 14 May 2015 14:13:35 GMT

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;

Re: Proc export .csv with leading zeros

Babloo — Fri, 15 May 2015 08:53:47 GMT

Thank you!

What is '09'x in your cats function?

Re: Proc export .csv with leading zeros

Ksharp — Fri, 15 May 2015 12:49:59 GMT

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

Re: Proc export .csv with leading zeros

Cynthia_sas — Fri, 15 May 2015 14:15:04 GMT

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