topic Re: Help adding leading zeros to .txt output in ODS and Base Reporting

Help adding leading zeros to .txt output

Patty — Fri, 10 Jan 2014 23:25:44 GMT

I am writing a program to output a SAS data set to a .txt file. The issues this certain field in SAS is a character field with numbers which normally would not be a problem except that in the out output they want leading zeros added to the field. I have to problem changing the character field to a numeric field either. Which ever way I can add and display the leading zeros. I've tried the tagattr format that I've used when outputting to excel but this is not working. Any ideas would be helpful.

Re: Help adding leading zeros to .txt output

jakarman — Sat, 11 Jan 2014 14:23:09 GMT

sure it is char

method 1

- SAS(R) 9.3 Functions and CALL Routines: Reference (right align)

- SAS(R) 9.3 Functions and CALL Routines: Reference (translate)

method 2

- convert to number (input fucntion) and use the numeric format (Base sas limt to ca 12 digits precision)

- SAS(R) 9.3 Formats and Informats: Reference (Zw.d format)

method ?

Re: Help adding leading zeros to .txt output

Tom — Sat, 11 Jan 2014 14:29:17 GMT

If you are writing to a text file there is no way to attach any attributes.

If the values inside the character variables have leading zeros then they will be preserved by SAS, if you are not seeing them then the problem is with the tool you are using to view the text file.

If the values do not have leading zeros then you will need to convert them.

So if your character variable is length 10 and all values are integers then you could use something like this:

myvar = put(input(myvar,10,),z10.);

Re: Help adding leading zeros to .txt output

Cynthia_sas — Sat, 11 Jan 2014 16:19:36 GMT

Hi:

As Tom explained, if you create ASCII text file, such as a CSV file, that has leading zeroes and then open the file with Excel, I believe that Excel will not respect leading zeroes. So you will either need to adjust the value in Excel or you will need to change the method that you use to create your output. For example, if you run the code below, you WILL see zeroes if you open the CSV file in Notepad, but you will NOT see zeroes if you open the CSV file with Excel. Either the MSOFFICE2K output (which is HTML, simply named as XLS to "fool" the Windows registry into launching Excel) or the TAGSETS.EXCELXP output (which is XML) will show zeroes in Excel.

Cynthia

ods csv file='c:\temp\leadzero_comma.csv';
proc print data=sashelp.class;
** 1) will *NOT* see zeroes in Excel;
** *WILL* see zeroes in Notepad -- which means that SAS did the right thing ;

** and EXCEL is not respecting the leading zeroes;
var name age height;
format age z5. height z8.2;
run;
ods csv close;

ods msoffice2k file='c:\temp\leadzero_mso.xls' style=sasweb;
proc print data=sashelp.class;
** 2) will see zeroes in Excel;
var name;
var age / style(column)={htmlstyle='mso-number-format:00000'};
var height/ style(column)={htmlstyle='mso-number-format:00000.00'};
run;
ods msoffice2k close;

ods tagsets.excelxp file='c:\temp\leadzero_xp.xml' style=sasweb;
proc print data=sashelp.class;
** 3) will see zeroes in Excel;
var name;
var age / style(column)={tagattr='00000'};
var height/ style(column)={tagattr='00000.00'};
run;
ods tagsets.excelxp close;

Re: Help adding leading zeros to .txt output

Vish33 — Mon, 13 Jan 2014 12:01:42 GMT

Hi,

I faced this problem in my previous report and it solved by giving the tagattr="format:@" to get the leading zero's in excel.

define 'Co-op'n / display "Co-Op" style(column)=[ indent=4 tagattr="format:@" just=center background=cxffffff foreground=stbr] ;

Regards,

Vish

Re: Help adding leading zeros to .txt output

Patty — Thu, 16 Jan 2014 17:47:02 GMT

Thanks Tom,

This worked. I just had to make 1 adjustment: myvar=put(input(myvar,10.)z10);

Thanks everyone, All the suggestions opened up possible answers to other issues.