How does SAS determine the default length of a character
in [SAS]
|
From: "Data _null_;" on 23 Oct 2009 16:50 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: Ya Huang on 23 Oct 2009 17:14 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 17:22 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 17:57 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 18:01 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 > >> > > > >
|
Next
|
Last
Pages: 1 2 3 Prev: Adding Previous Observations Next: It's Not Easy Being a SAS Programmer! |