topic Re: leading zeros in SAS Programming

leading zeros

mauri0623 — Mon, 23 Sep 2019 14:20:14 GMT

I create a .csv file from a SAS dataset. When I do this I loose the leading zeros on columns IPAC where the IPAC starts with 0000 and then the number.

Example, 00003801 will becomes 3801 and I loose the first 4 zeros. Any ideas how to see the leading zeros in the .csv file.

Re: leading zeros

Patrick — Mon, 23 Sep 2019 14:31:28 GMT

@mauri0623

Are you opening the .csv with a text editor or with Excel?

Excel will read the string as a number and omit the leading zeroes. Excel doesn't really show you what's in the .csv but it will interpret the values in the .csv and convert it to Excel.

If the zeroes are already missing in the .csv text file itself (opened with a text editor like Notepad) then use a Zn. format for writing the numbers.

Re: leading zeros

mauri0623 — Mon, 23 Sep 2019 14:41:29 GMT

Opening the .csv file in notepad ++ still shows the leading zeros missing.

Re: leading zeros

Cynthia_sas — Mon, 23 Sep 2019 14:45:40 GMT

Hi:
IF you are creating output for Excel, you have more choices than creating a CSV file. You have ODS CSV (plain text CSV file with no formatting), ODS TAGSETS.EXCELXP (Spreadsheet XML format 2003) and ODS EXCEL (XLSX format 2007).

If you are running SAS 9.4 then you should try ODS EXCEL. It will create an XLSX file that does respect the Zn. format, as shown in the output example below:

Cynthia

Re: leading zeros

mauri0623 — Mon, 23 Sep 2019 14:56:38 GMT

I am putting out a .csv file and not an excel file. I found the solution and it work. Basically using options will solve the issue.

ods _all_ close;

%let my_csv_file = C:/users/Mauri.Esfandiari/Documents/Missinginfo_to_Validate_&timestamp..csv;

ods csvall file = "&my_csv_file." options (prepend_equals="yes"

quote_by_type="yes");

proc print data = sas_perm.miss_ipac_odn_acc_0919 noobs;

title "Missing IPACs, ODNs, Accounting, and psudo IPACs";

run;

ods csvall close;

Re: leading zeros

Tom — Mon, 23 Sep 2019 15:45:10 GMT

How does that actually solve the problem?

You seem to have just added two extra lines above the actual data with a tile and a bunch of unneeded quotes around values that don't actually contain any delimiters that would make the quotes necessary.

Re: leading zeros

ballardw — Mon, 23 Sep 2019 16:56:31 GMT

@mauri0623 wrote:

Opening the .csv file in notepad ++ still shows the leading zeros missing.

If you first opened in Excel and then saved it, such as after widening columns to see values then Excel likely "corrupted" the values.

You did not say that you never opened the file in Excel.