topic Re: changing variable type when reading excel data in Microsoft Integration with SAS

changing variable type when reading excel data

deleted_user — Wed, 21 Feb 2007 21:05:53 GMT

I've assigned a spreadsheet to a libname (libname f "xyz.xls") and am using a data step to read in 2 worksheets.

One of the columns starts off and is mostly numeric, but occationally has text in it. SAS brings it in as numeric.

I would like to read the column as text. Is there any way to override the default behaviour?

Thank You,
Richard Wright

Re: changing variable type when reading excel data

Cynthia_sas — Wed, 21 Feb 2007 21:27:47 GMT

Richard...
If you're using the Libname Excel engine, you can specify the MIXED=YES option on the import. This will read the "mixed" type column as character data. There is a side effect that the use of the MIXED= option causes the Excel workbook to be in READONLY mode (according to the doc).

This option seems designed to do exactly what you want.
cynthia

Re: changing variable type when reading excel data

deleted_user — Wed, 21 Feb 2007 21:58:57 GMT

Thanks, Cynthia.

I tried that and it didn't seem to work.

Maybe, I'll go back and try it again.

Right now, I've resorted to import with lots of options, but that seems to be doing the same thing. The problem seems to stem from text added after the number - it looks like SAS sees the number, the blank after the number and blows off the remaining text.

And I thought this was going to be a snap.

Re: changing variable type when reading excel data

deleted_user — Wed, 21 Feb 2007 22:44:57 GMT

I revisited the MIXED=YES option and here's what I found:

I am trying to read two sheets:

SET ldrs.'timesheeting$'N(IN=t KEEP=_last_name first_name position__ Approvers_Position__ Approver_s_Name RENAME=(Approvers_Position__= certifiers_position__ Approver_s_Name=name_of_certifier))
ldrs.'100% ILS-CRS$'N(IN=p KEEP=_last_name first_name position__ certifiers_Position__ Name_of_Certifier);

If I run the SET statement as is, I generate an error message about position__ being defined as numeric and character.

If I comment out the second data set (100%), things run as (more or less) expected.

Re: changing variable type when reading excel data

Cynthia_sas — Thu, 22 Feb 2007 03:48:36 GMT

Richard:
It may be time to contact Tech Support with help on this. My importing of spreadsheets is mostly limited to single worksheets....unless somebody else has an idea.
cynthia

Re: changing variable type when reading excel data

deleted_user — Thu, 22 Feb 2007 15:15:08 GMT

I took care of it:
renamed the one var to pos1 and pos2 respectively and used a combination of in= processing and ifc(vtype()=) that seems to be doing the trick (although I am puzzled by one facet of this - but that's for another day).

Thanks for the help, Cynthia