topic Re: Importing data from Excel--only first 255 columns brought in in SAS Procedures

Importing data from Excel--only first 255 columns brought in

rchd — Wed, 22 Mar 2017 02:50:14 GMT

Hi All,

I am new to SAS and am having trouble getting my data into SAS from Excel. I have tried it with both libname and proc import (code below) and both sort of work (e.g., I get no errors), but neither brings in the entire data set, just the first 255 columns. I have re-ordered the columns in the Excel file, made sure all variables were formatted as numbers in the Excel file, and made sure that all missing values are indicated by a dot with no effect. The original data has 2970 rows and 412 columns. So far I get a SAS dataset with 2970 rows but only 255 columns. The Excel file has several sheets in it but the only one I need is the one called "Data". Also, I assembled the data in the Excel file myself and have used it in Stata, so I am confident that there are not weird formatting things in it (like non-numerical values mixed with numbers, or incompatible variable names). I am using SAS 9.4 for Windows (32 bit), SAS Enterprise Guide 7.1 (64-bit) and Excel 2016 if that helps.

So, how can I get my entire dataset into SAS from Excel? I realize that it is possible to import data in other formats, but I do a lot of work in Excel and it would be really convenient to be able to import from Excel directly, and everything I have read says that it is possible to do so, and it seems like I am really close to getting it to work. What am I missing?

Thank you!

~Riva

The two bits of code that give me exactly the same result:

libname fsafrica excel 'C:\Users\...\FS Africa Dataset 1960-2014.xlsx' mixed=yes;
	Options validVarName=any; 
data fsdata; 
set fsafrica."Data$"n; 
run;

proc import datafile='C:\Users\...\FS Africa Dataset 1960-2014.xlsx'
	DBMS=EXCEL replace out=fsafrica;
	sheet="Data$"n;
	getnames=yes;
data fsdata; 
set fsafrica; 
run;

Re: Importing data from Excel--only first 255 columns brought in

Reeza — Wed, 22 Mar 2017 03:05:51 GMT

What version of SAS are you using?

Change the DBMS to XLSX in either methods should work.

If you need sample code, search on here for this topic (Excel, 255 columns) and there are examples within the last month for sure.

EDIT: Saw that you inciuded your SAS version, changing the DBMS to XLSX will fix the issue.

Re: Importing data from Excel--only first 255 columns brought in

Ksharp — Wed, 22 Mar 2017 03:40:48 GMT

try range= option.

proc import datafile='C:\Users\...\FS Africa Dataset 1960-2014.xlsx'
	DBMS=EXCEL replace out=fsafrica;
	RANGE="Data$A1:Z100";
	getnames=yes;

Re: Importing data from Excel--only first 255 columns brought in

rchd — Wed, 22 Mar 2017 15:09:53 GMT

Thank you, Reeza! That worked

For the reference of others I also had to change the way I called the sheet (namely removing the $ at the end), otherwise I got an error saying that the sheet didn't exist or could not be found.

The working code now looks like this:

libname fsafrica XLSX 'C:\Users\...\FS Africa Dataset 1960-2014.xlsx';
	Options validVarName=any; 
data fsdata; 
set fsafrica.Data; 
run;

And this:

proc import datafile='C:\Users\...\FS Africa Dataset 1960-2014.xlsx'
	DBMS=XLSX replace out=fsafrica;
	sheet="Data"n;
	getnames=yes;
data fsdata; 
set fsafrica; 
run;

Obviously I will just pick one bit of code to use but now I know how to do it both ways

Re: Importing data from Excel--only first 255 columns brought in

rchd — Wed, 22 Mar 2017 15:14:38 GMT

Thank you for the suggestion! Unfortunately this didn't work while the DBMS = EXCEL. It still only took the first 255 columns. Once I changed the code to use XLSX the range option wasn't needed.