Help using Base SAS procedures

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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:

1)

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

 

2)

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;

 


Accepted Solutions
Solution
‎03-22-2017 11:01 AM
Super User
Posts: 17,831

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

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎03-22-2017 11:01 AM
Super User
Posts: 17,831

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

[ Edited ]

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.

New Contributor
Posts: 3

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

Thank you, Reeza! That worked Smiley Very Happy

 

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 Smiley Happy

Super User
Posts: 9,681

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

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;
New Contributor
Posts: 3

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

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.
☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 190 views
  • 1 like
  • 3 in conversation