Date:         Thu, 19 Sep 1996 02:10:46 GMT
Reply-To:     Rdsdnen <rasanen@SPRYNET.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Rdsdnen <rasanen@SPRYNET.COM>
Organization: Sprynet News Service
Subject:      Re: SAS & Excel files...

Evan,
 
There are several documented samples in the SAS help files, here's a
couple:
 
Unless the HOTLINK option is specified, DDE is performed as a single data
transfer.  That is, the values currently stored in the spreadsheet cells
are those that are transferred.  If the HOTLINK option is specified, the
DDE link is activated every time the data in the specified spreadsheet
range are updated.  In addition, DDE enables you to poll the data when the
HOTLINK option is specified to determine whether data within the range
specified have been changed.  If no data have changed, the HOTLINK option
returns a record of 0 bytes.  In the following example, row 1, column 1 of
the spreadsheet SHEET1 contains the daily production total.  Every time the
value in this cell changes, the SAS System reads in the new value and
outputs the observation to a data set.  In this example, a second cell in
row 5, column 1 is defined as a status field.  Once the user completes data
entry, typing any character in this field terminates the DDE link:
 
  /* Enter data into Excel SHEET1 in row 1 column 1. When you  */
  /* are through entering data, place any character in row 5   */
  /* column 1, and the DDE link is terminated.                 */
 filename daily dde 'excel|sheet1!r1c1' hotlink;
 filename status dde 'excel|sheet1!r5c1' hotlink;
 data daily;
  infile status length=flag;
  input @;
  if flag ne 0 then stop;
  infile daily length=b;
  input @;
     /* If data have changed, then the incoming record length  */
     /* is not equal to 0.                                     */
  if b ne 0 then
     do;
        input total $;
        put total=;
        output;
     end;
 run;
 
You can use DDE to read data from an Excel application into the SAS System,
as in the following example:
 
  /* The DDE link is established using Microsoft Excel        */
  /* SHEET1, rows 1 through 10 and columns 1 through 3.       */
 filename monthly dde 'excel|sheet1!r1c1:r10c3';
 data monthly;
  infile monthly;
  input var1 var2 var3;
 run;
 proc print;
 run;
 
Load an instance of the OLE Automation class and invoke Excel.  Set the
object to Visible so you can see the automation in progress.    LAUNCHXL:
hostcl = loadclass('sashelp.fsp.hauto');
call send(hostcl, '_NEW_', excelobj, 0,  'Excel.Application.5');
call send(excelobj, '_SET_PROPERTY_', 'Visible', 'True');
return;
Get the identifier for the current Workbooks property and add a worksheet.
Then get the identifier for the new worksheet.          CREATEWS:
call send(excelobj, '_GET_PROPERTY_', 'Workbooks', wbsobj);
call send(wbsobj, '_DO_', 'Add' );
call send(excelobj, '_GET_PROPERTY_', 'ActiveSheet', wsobj);
 
Open a SAS data set.    dsid=open('sasuser.class','i');
call set(dsid);
rc=fetch(dsid);
nvar=attrn(dsid, 'NVARS');
nobs=attrn(dsid, 'NOBS');
Traverse the data set and populate the cells of the Excel worksheet with
its data, row by row.   do col=1 to nvar;
call send(wsobj, '_COMPUTE_', 'Cells', 1, col, retcell);
var=varname(dsid,col);
call send(retcell,'_SET_PROPERTY_', 'Value' ,var);
end;
do while (rc ne -1);
 do row = 1 to nobs;
  do col = 1 to nvar;
   r=row+1;
   call send (wsobj, '_COMPUTE_', 'Cells', r ,col, retcell);
   if vartype(dsid,col) eq 'N' then
      var=getvarn(dsid,col);
   else var=getvarc(dsid,col);
   call send(retcell, '_SET_PROPERTY_', 'Value' ,var);
  end;
  rc=fetch(dsid);
 end;
end;
return;
Close the worksheet and end the Excel session. The _TERM_ method deletes
the OLE automation instance.    call send(excelobj,'_GET_PROPERTY_',
'ActiveWorkbook', awbobj);
call send(awbobj,  '_DO_', 'Close', 'False');
call send(excelobj, '_DO_', 'Quit');
call send(excelobj, '_TERM_');
return;
--
 
rasanen@sprynet.com
http://home.sprynet.com/sprynet/rasanen/
 
Evan Cooch <cooch@fraser.sfu.ca> wrote in article
<51pvrn$b0m@morgoth.sfu.ca>...
> For some reason, the students in one of our grad labs insist on
> maintain their data files in Excel. While it is fairly trivial to
> write out a flat ASCII file, with one form of column delimiter or
> other, and then read it into SAS, I 'm wondering if there is some
> facility in SAS (v. 6.11) for "directly" ready an Excel file (or Lotus
> 1-2-3, or dBase or anything else).
>
> Any help appreciated.
>
>
>
>
>