How does SAS determine the default length of a character
in [SAS]
|
From: "Huang, Ya" on 23 Oct 2009 18:11 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(a)gmail.com] Sent: Friday, October 23, 2009 3:01 PM To: Huang, Ya Cc: SAS-L(a)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(a)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(a)gmail.com] Sent: Friday, October 23, 2009 2:23 PM To: Huang, Ya Cc: SAS-L(a)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(a)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(a)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(a)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 >>
From: Joe Matise on 23 Oct 2009 19:28 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(a)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(a)gmail.com] > Sent: Friday, October 23, 2009 3:01 PM > To: Huang, Ya > Cc: SAS-L(a)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(a)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(a)gmail.com] > Sent: Friday, October 23, 2009 2:23 PM > To: Huang, Ya > Cc: SAS-L(a)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(a)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(a)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(a)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 > >> > > > > > > >
From: Joe Matise on 23 Oct 2009 19:41 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(a)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(a)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(a)gmail.com] >> Sent: Friday, October 23, 2009 3:01 PM >> To: Huang, Ya >> Cc: SAS-L(a)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(a)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(a)gmail.com] >> Sent: Friday, October 23, 2009 2:23 PM >> To: Huang, Ya >> Cc: SAS-L(a)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(a)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(a)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(a)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 >> >> >> >> >> >> >> >> >> >
From: "Huang, Ya" on 23 Oct 2009 19:44 Good question, I was wondering why it shows READ ONLY mode. I thought it might be because my testing file was based on an old excel file, which might be read only. So I did again, by creating a testing file totally from scratch, and it has the same result (Failed to scan....), it also still shows as READ ONLY mode! I'll send you my testing file in a separated file. It is EXCEL 2003 version. ________________________________ From: Joe Matise [mailto:snoopy369(a)gmail.com] Sent: Friday, October 23, 2009 4:28 PM To: Huang, Ya Cc: SAS-L(a)listserv.uga.edu Subject: Re: How does SAS determine the default length of a character variable read in from Excel. 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(a)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(a)gmail.com] Sent: Friday, October 23, 2009 3:01 PM To: Huang, Ya Cc: SAS-L(a)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(a)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(a)gmail.com] Sent: Friday, October 23, 2009 2:23 PM To: Huang, Ya Cc: SAS-L(a)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(a)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(a)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(a)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 >>
From: Joe Matise on 23 Oct 2009 21:12 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(a)amylin.com> wrote: > Joe, > > Can you test this and let me know? > > Thanks > > Ya > > ------------------------------ > *From:* Joe Matise [mailto:snoopy369(a)gmail.com] > *Sent:* Friday, October 23, 2009 4:41 PM > > *To:* Huang, Ya > *Cc:* SAS-L(a)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(a)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(a)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(a)gmail.com] >>> Sent: Friday, October 23, 2009 3:01 PM >>> To: Huang, Ya >>> Cc: SAS-L(a)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(a)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(a)gmail.com] >>> Sent: Friday, October 23, 2009 2:23 PM >>> To: Huang, Ya >>> Cc: SAS-L(a)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(a)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(a)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(a)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 >>> >> >>> >>> >>> >>> >>> >>> >>> >> >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Adding Previous Observations Next: It's Not Easy Being a SAS Programmer! |