DATA Step, Macro, Functions and more

sas 9.4 won't read some variables in excel file

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

sas 9.4 won't read some variables in excel file


I have text file database. I export it to an excel file.  I run SAS 9.4 using excel file. Then, SAS 9.4 won't read some variables in excel file with ERROR: Variable xx  not found.

Thanks in advance for your help.


Accepted Solutions
Solution
‎08-22-2014 05:58 PM
Super User
Super User
Posts: 6,500

Re: sas 9.4 won't read some variables in excel file

You need to tell SAS that it is an XLSX file instead of an "EXCEL" file.

This example with 301 variables worked fine for me.

data example;

  do id=1 to 2; output; end;

  array x(300);

run;

proc export data=example replace  dbms=xlsx

  outfile='c:\downloads\example.xlsx' ;

run;

proc import file='c:\downloads\example.xlsx' replace

   dbms=xlsx out=example2;

run;

View solution in original post


All Replies
Super User
Posts: 17,819

Re: sas 9.4 won't read some variables in excel file

You have to provide more information than that.

How did you import the file for example?

Why change from text file to Excel in the first place?

Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file

The text file database using unix  is not working. I have to enter some data using exported excel file from SAS.

now I use:

IMPORT OUT= WORK.A
DATAFILE= "c:\B.xlsx"

DBMS=EXCEL REPLACE;

SHEET="sheet1$";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Then run analysis.

Thanks,

Super User
Posts: 10,500

Re: sas 9.4 won't read some variables in excel file

Depending on what someone has done with Excel you may have blank columns but the import procedure tries to read them. Especially if there are multiple columns without a header then there may be this message. You can export the sheet to csv and see if you have columns of nothing but ,,, at the end of the data to confirm this is the issue, use a text editor to view the result (since Excel won't show the commas)

Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file


All columns have headers. Those variables won't read by SAS 9.4, looks like similar for those variables can be read by SAS.

but I will try to export to csv to check. Thanks.

Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file


HI Reeza, do you have better solutions? I rather use excel file to do analysis.

Thanks,

Respected Advisor
Posts: 4,646

Re: sas 9.4 won't read some variables in excel file

You can give Excel a hint about how to export your columns by assigning Number and Text formats to your workbook columns. Changing to MIXED=YES could also help you. Some numeric columns might be read as character but at least you will see what's going on.

PG

PG
Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file


I tried everything suggested by you guys, it did not work. I have total 812 variables, but SAS 9.4 only read 255. Other variables identified by SAS as  ERROR: Variable xx  not found.

Is there a way to let SAS 9.4 to read more variables?

Thanks

Super User
Posts: 10,500

Re: sas 9.4 won't read some variables in excel file

Is the Excel file XLS or XLSX? 255 sounds like an XLS limitation.


Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file

It is xlsx.

Super User
Super User
Posts: 6,500

Re: sas 9.4 won't read some variables in excel file

Is the name of the variable really XX or some other two character string?

Is it possible that it is a name that is confusing to Excel because it looks like a cell reference?

I have had trouble with creating XLSX files with short sheet names that looked too much like cell references.

Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file

I have  many variables' name like CA, CA1 CA2. SAS 9.4 can read 255 variable names like that. I am not sure that is the reason.

Thanks,

Solution
‎08-22-2014 05:58 PM
Super User
Super User
Posts: 6,500

Re: sas 9.4 won't read some variables in excel file

You need to tell SAS that it is an XLSX file instead of an "EXCEL" file.

This example with 301 variables worked fine for me.

data example;

  do id=1 to 2; output; end;

  array x(300);

run;

proc export data=example replace  dbms=xlsx

  outfile='c:\downloads\example.xlsx' ;

run;

proc import file='c:\downloads\example.xlsx' replace

   dbms=xlsx out=example2;

run;

Frequent Contributor
Posts: 82

Re: sas 9.4 won't read some variables in excel file

TOM,

I tried your way, it worked very well. Thanks.

Valued Guide
Posts: 3,208

Re: sas 9.4 won't read some variables in excel file

The 255 limit is no problem of SAS, it is Microsoft DAC JET ACE.

Obviously they did not designed in the origins for more serious data processing. Who is that mad using more than 255 columns let us use one byte for the counter.  

37612 - The SAS® System can only import and export 255 variables when you access a Microsoft Excel 2... If you try to search at Microsoft you get a lot of hits with a lot of non SAS products. Mickeysoft (keep it small)

The xlxs new interface is better as it using a zipped xml approach. But the interface rebuilding is on the road at SAS (work in progress).

---->-- ja karman --<-----
☑ This topic is SOLVED.

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

Discussion stats
  • 14 replies
  • 2007 views
  • 7 likes
  • 6 in conversation