topic Re: Leading Zero Truncating csv export in New SAS User

Leading Zero Truncating csv export

ashoks321 — Sun, 10 Feb 2019 16:17:57 GMT

Hello,

I'm trying to export a SAS table as a CSV file using proc export. I'm not sure why the leading zeros is getting truncated in one of the columns.

I tried to use the ODS csv option and also tried to export as a .txt file but still losing the leading zeroes.

I'm looked up the forums and I'm not sure I was able to find any other solution that works for me.

Just FYI, the file gets imported to a tableau workbook and so the end user is not able to search the data values as it's not accurate.

There are few other values that are longer than these missing values and they appear accurate so I'm not sure if the length is an issue.

Any help is much appreciated!

This is the proc export code I used.

PROC EXPORT
DATA= Data_Feb2019
OUTFILE="\path\Feb 2019 results.csv"
DBMS = csv REPLACE;
RUN;

Hoping to hear soon!

Ashok

Re: Leading Zero Truncating csv export

Tom — Sun, 10 Feb 2019 16:24:14 GMT

You need to ask that question on a Tableau forum.

SAS will not remove the leading zeros when it generates a CSV file. From either a character variable or a numeric variable that is formatted to display them.

Example:

data test;
  do i=0,1,2,10,12 ;
    z=i;
    format z z2.;
    char=put(i,Z2.);
    output;
  end;
run;

data _null_;
  file log dsd ;
  set test;
  put (_all_) (+0);
run;

455   data _null_;
456     file log dsd ;
457     set test;
458     put (_all_) (+0);
459   run;

0,00,00
1,01,01
2,02,02
10,10,10
12,12,12

Re: Leading Zero Truncating csv export

VDD — Sun, 10 Feb 2019 17:45:50 GMT

As Tom has shown you control the leading zeros using the .z

using @Tom code see how the zeros only pad the spaces in front of the numbers If your output number is larger than the format for the value a decimal is included and the number is converted.

data test;
  do i=0,1,2,10,12,112,456,9876,123456789 ;
    z=i;
    format z z8.;
    char=put(i,Z8.);
    output;
  end;
run;

data _null_;
  file log dsd ;
  set test;
  put (_all_) (+0);
run;

0,00000000,00000000

1,00000001,00000001

2,00000002,00000002

10,00000010,00000010

12,00000012,00000012

112,00000112,00000112

456,00000456,00000456

9876,00009876,00009876

123456789,1.2346E8,1.2346E8

Re: Leading Zero Truncating csv export

kiranv_ — Sun, 10 Feb 2019 18:17:16 GMT

if you have tableau desktop, you can directly connect to SAS.

Re: Leading Zero Truncating csv export

ashoks321 — Sun, 10 Feb 2019 18:43:35 GMT

Hi Tom, thanks for the reponse.
I did open the csv file and it seemed the zero truncated in that already. So I'm not totally sure it's a tableau issue.
I'll try the format changes you suggested.

Re: Leading Zero Truncating csv export

Tom — Sun, 10 Feb 2019 19:26:54 GMT

@ashoks321 wrote:
Hi Tom, thanks for the reponse.
I did open the csv file and it seemed the zero truncated in that already. So I'm not totally sure it's a tableau issue.
I'll try the format changes you suggested.

Then you probably opened the file with something else that doesn't understand it. Perhaps Excel? Open the file with a text editor.

filename csv temp;
PROC EXPORT
DATA= test
OUTFILE=csv
DBMS = csv REPLACE;
RUN;

data _null_;
 infile csv ;
 input;
 put _infile_;
run

i,z,char
0,01,00
1,01,01
2,01,02
10,01,10
12,01,12

Re: Leading Zero Truncating csv export

Tom — Sun, 10 Feb 2019 19:34:04 GMT

Just tell Tableau how to treat the field.

Tableau 10.5