Date:         Wed, 7 Mar 2007 10:25:26 -0500
Reply-To:     "data _null_;" <datanull@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "data _null_;" <datanull@GMAIL.COM>
Subject:      Re: Can import or dde identify merged cells in a spreadsheet?
Comments: To: Arthur Tabachneck <art297@netscape.net>
In-Reply-To:  <200703062346.l26LGK3H009667@mailgw.cc.uga.edu>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

I can't figure out a good way to represent at merge cell spread sheet
in e-mail.  If you are interested I can sent you a copy off list.

Here is what I came up with to read it and fill the merged cells as
requested.  I just changed the null cells to something other than
missing so I could id them.  The rest is just the same, mostly, as you
has posted to the original thread.

filename sas2xl dde 'excel|system';
data _null_;
   file sas2xl;
   sheet = 'sheet1';
   put '[workbook.activate(' sheet:$quote30. +(-1) ')]';
   put '[select("R1C1:R8C3")]';
   put '[formula.replace("","-999",2,2,false)]';
   run;

libname xl excel '.\mergedcells.xls' getnames=no;
data work.test;
   set XL.'Sheet1$'n;
   array f[*] f:;
   array t[3];
   retain t;
   do _n_ = 1 to dim(t);
      if not missing(f[_n_]) then t[_n_]=f[_n_];
      if t[_n_] eq -999 then t[_n_] = .;
      end;
   run;
proc contents varnum;
   run;
proc print;
   run;


View with fixed font.

Obs      F1      F2      F3    t1    t2    t3

1        1       1       2     1     1     2
2        1       .       2     1     1     2
3        1       .       .     1     1     2
4        1       2    -999     1     2     .
5     -999       .       .     .     2     .
6        .       .       3     .     2     3
7        .    -999    -999     .     .     .
8        2       3       4     2     3     4



On 3/6/07, Arthur Tabachneck <art297@netscape.net> wrote:
> Don Kline posed an interesting problem.
>
> Assume you have the following one column of data in an Excel spreadshhet:
>
> row col1
> 1    1
> 2    1
> 3    1
> 4    .
> 5    .
>
> However, rows 1 thru 3 are merged cells thus, when you import the
> spreadsheet, you end up with:
>
> row col1
> 1    1
> 2    .
> 3    .
> 4    .
> 5    .
>
> What you want is the original data, namely:
>
> row col1
> 1    1
> 2    1
> 3    1
> 4    .
> 5    .
>
> Anyone have any suggestion(s)?
>
> Art
>