topic Keeping leading zero in proc import multiple sheets in SAS Programming

Keeping leading zero in proc import multiple sheets

CathyVI — Tue, 07 Jan 2025 18:52:43 GMT

Hello,

I am importing excel sheets into SAS but values with leading zero are missing when imported. How can I resolve this issue.

%macro dataload(sheet);
proc import out=work.&sheet
    datafile="S:\document\inpatient.xlsx"
    dbms=xlsx replace;
    sheet="&sheet";
    getnames=yes;
run;
%mend dataload;
%dataload(cbcs);
%dataload(vhi);

For example, In excel 034567786 when imported will be 34567786. I can use data step infile statement to import the file but i have 2 sheets. How can I import each sheets using the infile statement or how can I manipulate the proc import statement so that it keeps the leading zeros? Thanks

Re: Keeping leading zero in proc import multiple sheets

Tom — Tue, 07 Jan 2025 19:12:14 GMT

@CathyVI wrote:

Hello,

I am importing excel sheets into SAS but values with leading zero are missing when imported. How can I resolve this issue.
%macro dataload(sheet);
proc import out=work.&sheet
    datafile="S:\document\inpatient.xlsx"
    dbms=xlsx replace;
    sheet="&sheet";
    getnames=yes;
run;
%mend dataload;
%dataload(cbcs);
%dataload(vhi);
For example, In excel 034567786 when imported will be 34567786. I can use data step infile statement to import the file but i have 2 sheets. How can I import each sheets using the infile statement or how can I manipulate the proc import statement so that it keeps the leading zeros? Thanks

If any of the cells in the column in the XLSX worksheet contain text values then the variable in SAS will be character. So if the cell contained the string 034567786 then the leading zero will be preserved. But if the cell contained the number 34,567,786 instead but had Excel formatting attached to display it as that 9 digit string then the leading zero will not come over.

And if all of the cells in the column in the XLSX worksheet is NUMERIC then the variable in SAS will be NUMERIC also.

To print the number 34,567,786 as the string 034567786 attach the Z9. format specification to the variable.

So if the variable in SAS is numeric than add a FORMAT statement.

data want;
  set have;
  format myvar z9.;
run;

And if the variable in SAS is character then you need to first make sure it has a length of at least 9 and then convert the digit string back into a number using the INPUT() function which you can then convert back into a string using the PUT() function.

data want;
   length myvar $9;
   set have;
   myvar = put(input(myvar,32.),Z9.);
run;

If you want something that works in both cases then make a NEW variable. Probably a character one since you want the leading zeros to be significant.

data want;
  set have(rename=(myvar=old_myvar));
  myvar = put(input(cats(old_myvar),32.),z9.);
run;

Not sure what you mean by data step and infile. You cannot read XLSX files that way. But if you have CSV files then you can. (Note that CSV files are NOT Excel files, just plain old TEXT files, even if your PC tries to open them automatically with Excel).

If you want to convert an XLSX file with multiple sheets into a CSV file then you will need a separate CSV file for each sheet. You could store them both into a ZIP if you wanted to share them as a single file.

Re: Keeping leading zero in proc import multiple sheets

ballardw — Tue, 07 Jan 2025 20:05:10 GMT

The only method I have had any luck with maintaining Excel values is to export the sheets to CSV file(s) and then use a data step to read the resulting files setting the values that Excel likely had as numeric as character to maintain the leading 0 in SAS.

If the sheets have the same structure then read the separate files by changing the infile file name and the Data set created.

Re: Keeping leading zero in proc import multiple sheets

Ksharp — Wed, 08 Jan 2025 01:40:21 GMT

You can import these columns/variables of Excel as CHARACTER variables to retain those leading zero.

1) the first way is using PROC IMPORT:

proc import datafile = "c:\temp\date.xlsx" out =have replace dbms =excel ;
dbdsopts="dbsastype=(age='numeric' weight='char(20)' )";
run;

2) the second way is using LIBNAME:

libname x excel 'c:\temp\date.xlsx';
data have2;
set x.'date$'n(dbsastype=(age='numeric' weight='char(20)'));
run;