Date: Fri, 23 Oct 2009 20:12:12 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: How does SAS determine the default length of a character
variable read in from Excel.
In-Reply-To: <B84879D70E8C1C418A6C8DA90F73313403B73CC2@A-EXCH-VS1.amylin.com>
Content-Type: text/plain; charset=ISO-8859-1
Yeah, it works-ish for me (1024 long). Still doesn't go over that, but I
think that's normal. No read only for me.
-Joe
On Fri, Oct 23, 2009 at 6:45 PM, Huang, Ya <Ya.Huang@amylin.com> wrote:
> Joe,
>
> Can you test this and let me know?
>
> Thanks
>
> Ya
>
> ------------------------------
> *From:* Joe Matise [mailto:snoopy369@gmail.com]
> *Sent:* Friday, October 23, 2009 4:41 PM
>
> *To:* Huang, Ya
> *Cc:* SAS-L@listserv.uga.edu
> *Subject:* Re: How does SAS determine the default length of a character
> variable read in from Excel.
>
> At minimum, DBSASTYPE has some impact here. See the following:
> proc import file="c:\test1.xls" out=test dbms=excel replace;
> run;
>
> libname test excel "c:\test1.xls";
>
> data blah;
> set test.'Sheet1$'n(DBSASTYPE=col1='CHAR(15900)');
> len=length(col1);
> run;
>
> test1.xls has the following:
> col1
> (seventeen rows of short, 5-8 character long text strings)
> (one row of 1253 long characters, with lots of alt+enters)
>
> PROC IMPORT, or a bare libname statement, imports it at $255 precisely as
> you describe.
>
> However, the above code [after the proc import] works fine, to import it to
> $1024. It does not have any impact above $1024. - note that I put the
> absurd value 15900 - but I presume that to be a defect of the JET engine.
> This is in 9.1.3, by the way, not 9.2, as I don't have 9.2 installed on this
> PC.
>
> If you want to email me the spreadsheet (and it's not sensitive data or can
> be made unsensitive) feel free, I can see if the above works on it here...
> I don't get any messages about 'cannot determine text length or date type'
> or whatnot, by the way; I think that's related to the READ ONLY mode message
> you got. I did all of this with the file open in Excel, so I'm actually
> surprised that didn't occur - usually it throws a fit if I try to do
> something like that with it open, but who knows.
>
> The only caveat here is that I'm using Excel 2007; I did save it as .xls,
> but I don't know if 9.1.3 will still use the JET engine, or the ACE instead,
> to connect to .xls files. Could be a difference. I know that on a 9.2
> machine it will use ACE, not JET, if you have 2007 installed; but I'm fairly
> sure 9.1.3 used JET on my machine that has now 9.2 installed, as when I
> installed it I had to go change a different registry entry [and I'd had 2007
> installed since I learned about the registry entry trick].
>
> -Joe
>
> On Fri, Oct 23, 2009 at 6:28 PM, Joe Matise <snoopy369@gmail.com> wrote:
>
>> In my experience, when it connects in read-only mode, bad things almost
>> inevitably result, specifically with regards to SAS changing its mind on how
>> it imports columns. Is that possibly the issue? Is there any reason that
>> it is opening it read-only?
>>
>> Thanks,
>>
>> Joe
>>
>>
>> On Fri, Oct 23, 2009 at 5:11 PM, Huang, Ya <Ya.Huang@amylin.com> wrote:
>>
>>> Not working. Actually, I started another thread late of last July,
>>> and some people already suggested using these options. But it
>>> never worked for me. If someone want to test, I can send the excel file.
>>>
>>>
>>> 28 LIBNAME xx EXCEL 'h:\sas-l\widecolumn.xls' MIXED=YES
>>> DBMAX_TEXT=3000 ;
>>> NOTE: Data source is connected in READ ONLY mode.
>>> NOTE: Libref XX was successfully assigned as follows:
>>> Engine: EXCEL
>>> Physical Name: h:\sas-l\widecolumn.xls
>>> 29 DATA yy;
>>> 30 SET xx.'A$'n (DBSASTYPE=(B='char(1024)'));
>>> WARNING: Failed to scan text length or time type for column B.
>>> 31 RUN;
>>>
>>> NOTE: There were 18 observations read from the data set XX.'A$'n.
>>> NOTE: The data set WORK.YY has 18 observations and 2 variables.
>>> NOTE: DATA statement used (Total process time):
>>> real time 0.01 seconds
>>> cpu time 0.01 seconds
>>>
>>>
>>> 32 LIBNAME xx CLEAR;
>>> NOTE: Libref XX has been deassigned.
>>>
>>> ________________________________
>>>
>>> From: Joe Matise [mailto:snoopy369@gmail.com]
>>> Sent: Friday, October 23, 2009 3:01 PM
>>> To: Huang, Ya
>>> Cc: SAS-L@listserv.uga.edu
>>> Subject: Re: How does SAS determine the default length of a character
>>> variable read in from Excel.
>>>
>>>
>>> Try
>>> DBSASTYPE=(var=CHAR(1024))
>>> with var=your variable name.
>>>
>>> -Joe
>>>
>>>
>>> On Fri, Oct 23, 2009 at 4:57 PM, Huang, Ya <Ya.Huang@amylin.com> wrote:
>>>
>>>
>>> Ok, here is the test. The excel file is simpe: two columns,
>>> one of them has short string up to row 16, starts from row 17,
>>> this column has long string (500+), which is manually typed in,
>>> with some ALT+ENTER to get line break.
>>>
>>> Since the JET engine default guessing is only up to row 9. Row
>>> 17 is too late to guess, so this column is truncated.
>>>
>>> This won't work for either libname engine, nor proc import.
>>>
>>> I'm using v9.2 by the way.
>>>
>>> I did try change the registry on a non validated PC, the code
>>> works on that one.
>>>
>>> Note both Warning below.
>>>
>>>
>>>
>>> NOTE: AUTOEXEC processing completed.
>>>
>>> 1 LIBNAME xx EXCEL 'h:\sas-l\widecolumn.xls' MIXED=YES
>>> DBMAX_TEXT=3000 ;
>>> NOTE: Data source is connected in READ ONLY mode.
>>> NOTE: Libref XX was successfully assigned as follows:
>>> Engine: EXCEL
>>> Physical Name: h:\sas-l\widecolumn.xls
>>> 2 DATA yy;
>>> 3 SET xx.'A$'n;
>>> WARNING: Failed to scan text length or time type for column B.
>>> 4 RUN;
>>>
>>> NOTE: There were 18 observations read from the data set
>>> XX.'A$'n.
>>> NOTE: The data set WORK.YY has 18 observations and 2 variables.
>>> NOTE: DATA statement used (Total process time):
>>> real time 0.00 seconds
>>> cpu time 0.00 seconds
>>>
>>>
>>> 5 LIBNAME xx CLEAR;
>>> NOTE: Libref XX has been deassigned.
>>> 6
>>> 7 proc import datafile="h:\sas-l\widecolumn.xls"
>>> 8 out=mydata
>>> 9 replace;
>>> 10 getnames=yes;
>>> 11 /* GUESSINGROWS=50; this won't work for excel, only
>>> for
>>> delimited file */
>>> 12 run;
>>>
>>> WARNING: Failed to scan text length or time type for column B.
>>> NOTE: WORK.MYDATA data set was successfully created.
>>> NOTE: PROCEDURE IMPORT used (Total process time):
>>> real time 0.35 seconds
>>> cpu time 0.20 seconds
>>>
>>>
>>>
>>> ________________________________
>>>
>>> From: Joe Matise [mailto:snoopy369@gmail.com]
>>> Sent: Friday, October 23, 2009 2:23 PM
>>> To: Huang, Ya
>>> Cc: SAS-L@listserv.uga.edu
>>> Subject: Re: How does SAS determine the default length of a
>>> character
>>> variable read in from Excel.
>>>
>>>
>>>
>>> You should be able to use the various DBoptions to set variable
>>> type and
>>> length. If DBMAX_TEXT doesn't work [and please try it before
>>> deciding
>>> it won't work], DBSASTYPE should (it allows you to specify a
>>> length).
>>> Look under the LIBNAME options in SAS/ACCESS for PC files. If
>>> you have
>>> 9.2, you can use it in PROC IMPORT; if you have 9.1, I think you
>>> have to
>>> use LIBNAME access instead.
>>>
>>> -Joe
>>>
>>>
>>> On Fri, Oct 23, 2009 at 4:14 PM, Ya Huang <ya.huang@amylin.com>
>>> wrote:
>>>
>>>
>>> I've been working on this recently. Since I don't want to
>>> change
>>> the windows registry (our PC is considered validated, so
>>> nothing
>>> should be changed), I asked SI tech support to see if
>>> there is
>>> any ways around this. Unfortunately, I was told that
>>> there is no
>>> other ways.
>>>
>>> I don't belive dbmax_text option will have any impact,
>>> since
>>> this
>>> is as the original poster said, the windows thingy.
>>>
>>> My workaround right now is to go DDE, since I know how
>>> wide
>>> a column would be, I can specify it in the length
>>> statement.
>>>
>>> It would be much easier if this "BUG" is fixed, so that I
>>> can
>>> use the libname engine.
>>>
>>> Wonder if there is a VB script to control the guessing
>>> row of
>>> Jet engine?
>>>
>>>
>>> On Fri, 23 Oct 2009 15:50:20 -0500, Data _null_;
>>> <iebupdte@GMAIL.COM> wrote:
>>>
>>> >I think you need the data set option DBMAX_TEXT. It may
>>> work
>>> on the
>>> >LIBNAME statement too. See docs for all the details.
>>> >
>>> >On 10/23/09, Brian Wallace <brian_c_wallace@yahoo.com>
>>> wrote:
>>> >> There was a truncation error and I have to explain
>>> what
>>> happened. We're
>>> reading in Excel 2003 spreadsheets into SAS 9.1.3 SP 4
>>> using the
>>> libname
>>> method/Microsoft.Jet.OLEDB.4.0 engine.
>>> >>
>>> >> Without any manipulation, it seems that all character
>>> fields,
>>> regardless
>>> of length (i.e. > 255) are truncated to 255 once in SAS.
>>> However, I just
>>> ran a test program using an Excel spreadsheet with three
>>> cells
>>> that had 400
>>> characters in them and when read into SAS, those three
>>> had a
>>> length of 1024.
>>> >>
>>> >> Any help would be greatly appreciated. Or assistance
>>> in
>>> pointing me to
>>> an appropriate document where I could figure it out for
>>> myself
>>> would be
>>> greatly appreicated.
>>> >>
>>> >> Thanks to everyone and have a nice weekend,
>>> >>
>>> >> Brian Wallace
>>> >>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>
| 
