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

sas 9.4 won't read some variables in excel file

xinjian — Thu, 21 Aug 2014 22:09:49 GMT

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.

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

Reeza — Thu, 21 Aug 2014 22:25:05 GMT

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?

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

xinjian — Thu, 21 Aug 2014 22:36:39 GMT

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,

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

ballardw — Thu, 21 Aug 2014 22:44:38 GMT

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)

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

xinjian — Thu, 21 Aug 2014 22:57:05 GMT

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.

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

xinjian — Thu, 21 Aug 2014 23:10:35 GMT

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

Thanks,

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

PGStats — Fri, 22 Aug 2014 01:11:36 GMT

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.

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

xinjian — Fri, 22 Aug 2014 20:07:24 GMT

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

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

ballardw — Fri, 22 Aug 2014 20:32:32 GMT

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

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

xinjian — Fri, 22 Aug 2014 20:35:22 GMT

It is xlsx.

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

Tom — Fri, 22 Aug 2014 21:06:10 GMT

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.

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

xinjian — Fri, 22 Aug 2014 21:17:52 GMT

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,

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

Tom — Fri, 22 Aug 2014 21:58:16 GMT

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;

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

jakarman — Sat, 23 Aug 2014 05:55:02 GMT

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 2007 file 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).

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

xinjian — Tue, 26 Aug 2014 14:51:20 GMT

TOM,

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