SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Truncated columns imported from excel

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Truncated columns imported from excel

Hi,

I have an EXCEL spread sheet that has 3000 rows and 140 columns of data. The some columns are mixed types with both numeric and character data as wide as 5500 characters. I want to import all or even just some of the columns into SAS keeping the mixed data as character data. However, I don't know why some columns are imported correctly with no truncation, while others are being truncated by SAS. I have used the SAS import wizard and checked the MIXED data option, and also used the PROC IMPORT with MIXED=YES option.

For the widest column, which is 5500 characters wide and called FK_word, SAS imports this variable correctly and no log warnings are issued. However, for the second widest, which is 1500 characters wide and called FK_code, I get the following in the log: WARNING: Failed to scan text length or time type for column FK_code and the data is truncated to 255 characters on importing from excel. This truncation and log warnings issued for many variables, all of which get truncated at 255 characters, if the original excel column had more than that.

Can someone tell me why this is happening and how I can fix it? Why does SAS correctly import the widest column, but others are truncated during the importation process? Below are sample data for the two variables that I mention.

Thanks,

Ian.

Sample of data:

FK_code (import truncated at 255 characters): 4813 | 4822 | 3661 | 3577 | 3357 | 5065 | 5045 | 5063 | 5999 | 3571 | 3577 | 3572 | 3575 | 7372 | 7379 | 5045 | 3663 | 3674 | 3679 | 3651 | 3577 | 3661 | 3571 | 6719 | 3651 | 3639 | 3579 | 3663 | 3669 | 3679 | 3691 | 3829 | 3559 | 3651 | 3663 | 3577 | 394 (truncated by SAS at 255 characters).... but should be 1500 characters.

FK_word (imports 5500 character correctly): Telephone communications, except radiotelephone | Telegraph and other message communications | Telephone&telegraph apparatus | Computer peripheral equipment, nec | Drawing and insulating of nonferrous wire | Electronic parts and equipment, nec | Computers and peripheral equipment and software | Electrical apparatus and equip | Retail stores, nec | Electronic computers | Computer peripheral equipment, nec | Computer storage devices | Computer terminals | Prepackaged Software | Computer related services,nec | Computers and peripheral equipment and software | Radio & TV broadcasting & communications equipment | Semiconductors and related devices | Electronic components, nec | Household audio and video equipment | Computer peripheral equipment, nec | Telephone&telegraph apparatus | Electronic computers | Offices of holding companies, nec | Household audio and video equipment | Household appliances, nec | Office machines, nec | Radio & TV broadcasting & communications equipment | Communications equipment, nec | Electronic components, nec | Storage batteries | Measuring&controlling devices | Special industry machinery, nec | Household audio and video equipment | Radio & TV broadcasting & communications equipment | Computer peripheral equipment, nec | Games, toys, children's vehicles,exc. dolls, bikes | Phonograph records,prerecorded audio tapes & disks | Motion picture and video tape production | Personal credit institutions | Equipment rental and leasing, (... goes for 5500 character, but truncated by me for presentation here, not truncated by SAS).


Accepted Solutions
Solution
‎03-26-2012 07:40 AM
Frequent Contributor
Posts: 101

Truncated columns imported from excel

The following SAS notes explain the issue you are encountering.

http://support.sas.com/kb/33/257.html

http://support.sas.com/kb/19/409.html

The notes recommend editing the Windows registry to resolve the issue. If you are like me and use a company laptop that does not allow you to change the registry settings, then you must try an alternative. I think the simplest solution is to find the row in the worksheet that has the longest length of data for FK_code and move it to the first row. Then maybe SAS will correctly set the length of the variable.

View solution in original post


All Replies
Solution
‎03-26-2012 07:40 AM
Frequent Contributor
Posts: 101

Truncated columns imported from excel

The following SAS notes explain the issue you are encountering.

http://support.sas.com/kb/33/257.html

http://support.sas.com/kb/19/409.html

The notes recommend editing the Windows registry to resolve the issue. If you are like me and use a company laptop that does not allow you to change the registry settings, then you must try an alternative. I think the simplest solution is to find the row in the worksheet that has the longest length of data for FK_code and move it to the first row. Then maybe SAS will correctly set the length of the variable.

Contributor
Posts: 53

Re: Truncated columns imported from excel

Thanks SAS_Bigot!

I like you, cannot change the registries, so I searched in each column of my excel data for the cell with the widest character length, copied that into the first row of the excel. This ensured that when the scan was done, SAS picked up the widest length for that column. It was bit of a hassle doing that for each column, but it worked! Thanks very much!!

Regular Contributor
Posts: 222

Re: Truncated columns imported from excel

Thanks, the method works, but some time it is not convinience to change the excel before read it into SAS.

Is there any other method can do this but don't change the excel file ?

Thanks

Occasional Learner
Posts: 1

Re: Truncated columns imported from excel

You can take advantage of "Advanced" option in Access Import Wizard and set the length of each field then import Access file into SAS

Super Contributor
Posts: 358

Truncated columns imported from excel

Which DBMS are you using when reading he EXCEL data?

If you use DBMS=EXCEL in the PROC IMPORT, try using DBMS=XLS.

Supposedly, it examines the all the data to determine variable types and sizes, rather than the subset that DBMS=EXCEL uses.

Occasional Learner
Posts: 1

Truncated columns imported from excel

Even I have the same problem I tried to do that with both dbms = excel and dbms = xls even i get same error if i use dbms = excel but when i use xls it doesnt give me any errors but the data is not true like I get some dummy values lik        "o eto xmnto`"    instead of     'MCB 0903'     and the partern is not unique even it gives same dummy value on certain cells !! and i dont understand why

Post a Question
Discussion Stats
  • 6 replies
  • 9752 views
  • 2 likes
  • 6 in conversation