Importing XLSX files

[PetePatel]

Hi all,

I am having problems trying to mass import a number of monthly XLSX files into SAS.

I have tried the below script but getting an error:

proc import datafile="Location\Apr15.xlsx"
out=apr15 dbms=xlsx
replace;
run;

Error Log:

DBMS type XLSX not valid for import

Both of my SAS (9.4) and Excel (2016) are 64 bit.

The other thing I have tried is to mass convert the XLSX files into CSV first using a batch program but I am losing the leading zeros from a number of key variables.

What do you suggest?

Cheers,

Pete

[Reeza · Accepted solution]

Do you have a license for SAS/ACCESS to PC Files?

If not, then you don't have the appropriate tools to do this.

Check installed products:

proc product_status;run;

Check licensed products:

proc setinit;run;

---

@PetePatel wrote:

Hi all,

I am having problems trying to mass import a number of monthly XLSX files into SAS.

I have tried the below script but getting an error:

proc import datafile="Location\Apr15.xlsx"
out=apr15 dbms=xlsx
replace;
run;

Error Log:

DBMS type XLSX not valid for import

Both of my SAS (9.4) and Excel (2016) are 64 bit.

The other thing I have tried is to mass convert the XLSX files into CSV first using a batch program but I am losing the leading zeros from a number of key variables.

What do you suggest?

Cheers,

Pete

---

If your conversion is going wrong, try a different approach. There's a VBS script on the SAS Support page that could be easily modified. Typically Excel is the one that removes the leading zeroes while trying to be helpful.

[ballardw]

It appears that you may not have the SAS/Access Interface to PC Files module available.

I would actually go the CSV route with some addition. Use Proc Import to import one of the CSV files. The log will contain data step code used to read the file. Copy that data step from the log to the editor. Save it. Modify the data step code insure that the lengths of character variables are sufficient across all of your expected data, that the informats are correct (date instead of character or character instead of numeric such as your leading zero variable).

Then rerun the data step with corrections until you like the result. You could then read each file separately changing the infile statement to point to the different file and the output data set.

The are a number of posts on this forum for how to read multiple sets.

Depending on how your Excel files were created it is possible that your CSV will have "ghost" rows of data with all variables missing, so you may need to address that.