Importing XLSX files

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

Importing XLSX files

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


Accepted Solutions
Solution
‎03-16-2018 07:20 AM
Super User
Posts: 23,729

Re: Importing XLSX files

Posted in reply to PetePatel

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.

View solution in original post


All Replies
Solution
‎03-16-2018 07:20 AM
Super User
Posts: 23,729

Re: Importing XLSX files

Posted in reply to PetePatel

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.

Super User
Posts: 13,550

Re: Importing XLSX files

Posted in reply to PetePatel

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 320 views
  • 2 likes
  • 3 in conversation