topic Re: Import XLSX file in SAS Programming

Import XLSX file

s_manoj — Tue, 26 Dec 2017 17:21:11 GMT

Hi all,

I got a problem in importing excel file (.xlsx);

I have tried with porc import

here's the code:

proc import datafile = " D:\myfolder\sample.xlsx" dbms = excel out = output;

sheet = sheet1;

run;

by running this code sheet has been imported, but problem is, actually my sheet contains 12 variables, but SAS imported 24 columns in which 12 were empty coulmns and also after running this code I have'nt found any infile/input code in log; so, what might be the reason for this?

I want infile/input code to import excel file, please can someone write infile code to improt excel file !

Regards and Thank you in advance.

Re: Import XLSX file

Kurt_Bremser — Tue, 26 Dec 2017 08:50:19 GMT

Excel keeps track of all cells that have been manipulated at least once, and computes a virtual "sheet size" from that.

Delete all empty columns and rows up to where your import "ended", before you try the import again. That deletion will remove the "used" information from the cells.

Alternatively, save to csv from Excel and use a custom data step to only read the columns you want.

Re: Import XLSX file

Tom — Tue, 26 Dec 2017 15:38:57 GMT

If SAS created 24 variables then the XLSX sheet had 24 "used" columns. Best fix for that it is to update the XLSX file. But it is also easy to DROP the variables from your SAS dataset.

Not sure what you are talking about the LOG, but you will not see an INFILE statement used to read a structured binary file like an XLSX file like you would for reading a simple delimited text file like a CSV file.