Date:         Tue, 8 Apr 2003 15:40:30 -0400
Reply-To:     Howard_Schreier@ITA.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard_Schreier@ITA.DOC.GOV
Subject:      Re: Lost cards, Excel, and general plans

I think you are on the right track. I believe that the range of cells which
Excel passes to SAS via the DDE buffer depends not only on the range called
for in the DDE triplet, but also on the "last" cell in the worksheet. By
entering something in the cell below your data and then erasing it, you
move that last-cell marker down one row, and an additional empty line gets
passed to SAS.

Try adding the option

   end=nomore

to your INFILE statement and expand the loop termination condition thusly

   do until (var1=" " or nomore);

On Tue, 1 Apr 2003 13:28:51 -0500, Kevin Viel <kviel@EMORY.EDU> wrote:

>Greetings,
>
>        I have data in Excel grouped such that a set of rows acts as a
>header and the following rows contains the data of interest for that group.
>Between each group is an empty row:
>
>Header1
>header2
>data1
>data2
><EMPTY ROW>
>Header1
>header2
>data1
>data2
>data3
>
>The number of rows of data following the header is variable.  Therefore, I
>wrote a simple loop:
>
>data _null_;
>  infile ONE;
>  input var1;
>  if var1="Header1" then
>    do;
>        input;
>        do until (var1=" ");
>            input var1;
>        end;
>    end;
>run;
>
>I read using DDE from Excel.  SAS reports a LOST CARD.  This occurs in the
>cell following the last datum.  If I enter a number and then delete it,
the
>code executes as I had expected.  One question is whether an Excel cell
that
>never contained an entry is not part of the raw data to SAS (my infile
>statement definitely contains enough rows).  Could someone perhaps suggest
>an improvement to my strategy?  What might be a possible way to avoid
>needing to put data (including a space) into that cell?
>
>Thanks,
>
>Kevin
>
>
>____________________________________
>
>Kevin Viel
>Department of Epidemiology
>Rollins School of Public Health
>Emory University
>Atlanta, GA 30329