Prev: Adding Previous Observations
Next: It's Not Easy Being a SAS Programmer!
From: xlr82sas on 23 Oct 2009 23:09
On Oct 23, 6:12 pm, snoopy...(a)GMAIL.COM (Joe Matise) wrote:
> 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.
>
> -JoeOn Fri, Oct 23, 2009 at 6:45 PM, Huang, Ya <Ya.Hu...(a)amylin.com> wrote:
> >  Joe,
>
> > Can you test this and let me know?
>
> > Thanks
>
> > Ya
>
> >  ------------------------------
> > *From:* Joe Matise [mailto:snoopy...(a)gmail.com]
> > *Sent:* Friday, October 23, 2009 4:41 PM
>
> > *To:* Huang, Ya
> > *Cc:* SA...(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 <snoopy...(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.Hu...(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:snoopy...(a)gmail.com]
> >>> Sent: Friday, October 23, 2009 3:01 PM
> >>>  To: Huang, Ya
> >>> Cc: SA...(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.Hu...(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:snoopy...(a)gmail.com]
> >>>        Sent: Friday, October 23, 2009 2:23 PM
> >>>        To: Huang, Ya
> >>>        Cc: SA...(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.hu...(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_;
> >>>        <iebup...(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_wall....(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

see

T000225 CONVERTING ALL EXCEL CELLS TO CHARACTER BEFORE COVERTING TO A
SAS TABLE
T000230 READING LONG STRINGS FROM EXCEL CELLS (EVEN WHEN ALT-ENTER HAS
BEEN USDED)

http://homepage.mac.com/magdelina/.Public/utl.html
utl_tipweb.txt

There are other tips on my site to make EXCEL behave

If the data in excel is critical, here is what I do

1. Highlight the data rectangle and assign a named range (fat in this
case)
2. Run my VBA addticks macro to change all the cells to character
( see below)
3. Run native MS SQL code using SAS passthrouh to determine which
columns are pure character, pure numeric or mixed
4. Run another simple passthru MS SQL code to get the maximum lengths
of each column
5. Use Scantext=no dbmax=max col length +1k

Here are some examples

/* HOW MANY COLUMNS HAVE AT LEAST ONE CHARACTER CELL */
proc sql dquote=ansi;
connect to excel (Path="\\usal-home\regusers\local\utl\inp
\t000190_input.xls");
select * from connection to Excel
(
Select
count(*) + sum(isnumeric(fat1)) as numchrfat1,
count(*) + sum(isnumeric(fat2)) as numchrfat2,
count(*) + sum(isnumeric(fat3)) as numchrfat3,
count(*) + sum(isnumeric(fat4)) as numchrfat4
from
fat
);
disconnect from Excel;
quit;
/* FAT4 is the only numeric
numchrfat1 numchrfat2 numchrfat3 numchrfat4
2 2 2 0
*/
/* LETS GET THE MAX LENGTH OF EACH VARIABLE FIRST */
proc sql dquote=ansi;
connect to excel (Path="c:\tmp\fat.xls");
select * from connection to Excel
(
Select
max(len(fat1)) as lenfat1
,max(len(fat2)) as lenfat2
,max(len(fat3)) as lenfat3
from
fat
);
disconnect from Excel;
quit;
/* all cells have length 2709 bytes */
/* fat.xls has a named range called fat it is also a good idea to
run the addticks macro on the spreadsheet */
libname xls excel "\\usal-home\regusers\local\utl\inp
\t000190_input.xls" mixed=yes scan_text=no dbmax_text=4096 ver=2002;
data fatcel;
length fat1 fat2 fat2 $4096;
set xls.fat(dbsastype=
( Fat1 ='char(4096)'
Fat2 ='char(4096)'
Fat3 ='char(4096)'
Fat4 ='numeric'
));
lenfat1=lengthn(strip(fat1));
lenfat2=lengthn(strip(fat2));
lenfat3=lengthn(strip(fat3));
put lenfat1= lenfat2= lenfat3= fat4=;
run;
libname xls clear;
/* LENFAT1=3538 LENFAT2=3538 LENFAT3=3538 FAT4=1
LENFAT1=3538 LENFAT2=3538 LENFAT3=3538 FAT4=2 */

Sub AddTicks()
Dim LastPlace, Z As Variant, X As Variant
LastPlace = ActiveCell.SpecialCells(xlLastCell).Address
ActiveSheet.Range(Cells(1, 1), LastPlace).Select
Z = Selection.Address 'Get the address
For Each X In ActiveSheet.Range(Z) 'Do while
If Len(X) > 0 Then 'Find cells with something
X.FormulaR1C1 = Chr(39) & Mid(X, 1, Len(X)) '39 is
code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick
End If
If X = "TIUQ" Then
Exit Sub
End If
Next
End Sub
First  |  Prev  | 
Pages: 1 2 3
Prev: Adding Previous Observations
Next: It's Not Easy Being a SAS Programmer!