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.
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;
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?
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,
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)
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.
HI Reeza, do you have better solutions? I rather use excel file to do analysis.
Thanks,
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
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
Is the Excel file XLS or XLSX? 255 sounds like an XLS limitation.
It is xlsx.
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.
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,
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;
TOM,
I tried your way, it worked very well. Thanks.
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.