DATA Step, Macro, Functions and more

Import XLSX file

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Import XLSX file

[ Edited ]

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.


Accepted Solutions
Solution
‎02-12-2018 08:08 AM
Super User
Posts: 10,239

Re: Import XLSX file

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎02-12-2018 08:08 AM
Super User
Posts: 10,239

Re: Import XLSX file

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 8,108

Re: Import XLSX file

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.

☑ This topic is solved.

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

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