Date:         Tue, 6 Jun 2006 09:05:07 -0500
Reply-To:     "Workman, Rob" <Rob.Workman@SORIN-NA.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Workman, Rob" <Rob.Workman@SORIN-NA.COM>
Subject:      Re: Excel to SAS import dropping data?
Comments: To: "paulasims2004@hotmail.com" <paulasims2004@HOTMAIL.COM>
Content-Type: text/plain; charset="us-ascii"

Paula,

To get around this problem, I use this SCL program that imports
everything as character variables (200 length).  

Set the macro variable ws to the name of the worksheet and then run the
program:

%let ws = c:\temp\temp.xls;
Proc display c=work.xl.xlimport.scl;
Run;

Kind regards,

Rob Workman

import sashelp.fsp;

init:

  if not fileexist(symget('ws')) then
    do;
    put 'Set the macro variable ws to the filename of the workbook';
    return;
    end;

  dcl hauto xl= _neo_ hauto(0, 'Excel.Application');

  dcl object xlwb xlas xlusedrange xlrows xlcols xlcell;
  dcl num rows cols;
  dcl char(200) value;

* xl._setProperty('visible', 'true');
  xl._getProperty('workbooks', xlwb);

  xlwb._do('open', symget('ws'));

  xl._getproperty('activesheet', xlas);

  xlas._getproperty('usedrange', xlusedrange);

  xlusedrange._getproperty('rows', xlrows);
  xlrows._getproperty('count', rows);

  xlusedrange._getproperty('columns', xlcols);
  xlcols._getproperty('count', cols);

  *** create a dataset of the worksheet ***;
  id = open('ws', 'n');

  do ii = 1 to cols;
  rc = newvar(id, 'f'||putn(ii, 'z3.'), 'c', 200);
  end;
  rc = close(id);

  id = open('ws', 'u');


  do ii = 1 to rows;
  rc = append(id);
    do jj = 1 to cols;
    *** get the value from the cell;
    xlusedrange._getproperty('cells', ii, jj, xlcell);
    xlcell._getproperty('value', value);

    call putvarc(id, jj, value);
    end;
  rc = update(id);

  end;

  rc = close(id);


  put 'Table WS created';
  put '*** Program Complete ***';

return;
rc=rc;
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
paulasims2004@hotmail.com
Sent: Tuesday, June 06, 2006 7:24 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Excel to SAS import dropping data?

Hello all,
The situation I'm about to describe will have your eyes rolling but
unfortunately I'm "stuck" with this "process".

I generated a dataset and downloaded the results into an Excel file. As
is typical in SAS, the missing numeric values are dots. We want to keep
the missing variables missing. I then give the results (dots and all)
to a group of users who make changes to the data-set and return it to
me for more manipulation via SAS. This can go back and forth dozens of
times.

What I have noticed is sometimes when I import from Excel to SAS, some
values are missing (they are present in the original Excel sheet) but
the variables are there. I've tried eliminating the dots and sometimes
that does the trick and sometimes not. Sometimes I can catch such
things but the datasets are getting very long (100 variables, 20000
obs) so it's not always possible. My users can barely spell SAS let
alone use it so that's not an option. Finally, they can't give me the
changes so I can make them because that too would be a nightmare.

So my question is, has this happened to anyone and is there any way to
make sure that my imports and exports are "clean?" I usually use the
import wizard.

Thanks

Paula

----------------------------------------------------------------------------------------------
This message contains confidential information intended only for the use of the addressee(s). 
If you are not the addressee, or the person responsible for delivering it to the addressee, you 
are hereby notified that reading, disseminating, distributing or copying this message is strictly 
prohibited. If you have received this message by mistake, please notify us, by replying to the 
sender, and delete the original message immediately thereafter. Thank you.