BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
xinjian
Calcite | Level 5


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.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

14 REPLIES 14
Reeza
Super User

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?

xinjian
Calcite | Level 5

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,

ballardw
Super User

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)

xinjian
Calcite | Level 5


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.

xinjian
Calcite | Level 5


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

Thanks,

PGStats
Opal | Level 21

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
xinjian
Calcite | Level 5


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

ballardw
Super User

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


xinjian
Calcite | Level 5

It is xlsx.

Tom
Super User Tom
Super User

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.

xinjian
Calcite | Level 5

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,

Tom
Super User Tom
Super User

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;

xinjian
Calcite | Level 5

TOM,

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

jakarman
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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