All YOUR Questions Answered at
[sas.answers @ Holland Numerics]
WebRing
Sponsored Links for SAS Software Users
Careers, Jobs and Contracts, Services and Products

Frequently Asked Questions about SAS Software

The following Question & Answer List is based on SAS questions received by the author.

Using OLE with SAS Software

Question Answer
I have seen the example given by the SAS Institute in the help files for writing data to an Excel v5 spreadsheet, but can you write data from a SAS dataset into a 123 v9 spreadsheet using OLE? The following SAS version 8 code has similar functionality to the Excel example provided by the SAS Institute, but will also create a 123 spreadsheet called SASDATA.123 in your default directory. Note that the buttons on the Frame asscociated with this SCL should be called B_LAUNCH, B_CREATE, B_CLOSE and B_EXIT:
  length dsn $17 sheetfile $80 alpha $27 textcol $8;
  INIT:
    /* Initialise values */
    dsn='sashelp.class';
    sheetfile='sasdata.123';
    alpha=' abcdefghijklmnopqrstuvwxyz';
  return;
  MAIN:
  return;
  TERM:
    /* Close SAS table, if open */
    if dsid > 0 then dsid=close(dsid);
  return;
  B_LAUNCH:
    /* Open 1-2-3, and make visible */
    hostcl = loadclass('sashelp.fsp.hauto');
    call send (hostcl, '_new',sessobj, 0, 'Lotus123.Workbook.98');
    call send (sessobj, '_getProperty', 'Application', appobj);
    call send (appobj, '_setProperty', 'Visible', 'True');
  return;
  B_CREATE:
    /* Create new document */
    call send (appobj, '_do', 'NewDocument');
    call send (appobj, '_getProperty', 'ActiveDocument', adobj);
    /* Open SAS table, and count columns and rows */
    dsid=open(dsn, 'i');
    call set(dsid);
    rc=fetch(dsid);
    nvar=attrn(dsid, 'NVARS');
    nobs=attrn(dsid, 'NOBS');
    /* Write column names to 1st row of sheet */
    do col=0 to (nvar-1);
      sheetcol=col+1;
      sheetrow=1;
      link col2text;
      call send (adobj, '_getProperty', 'Ranges', trim(textcol), retcell);
      var=varname(dsid, sheetcol);
      call send (retcell, '_setProperty', 'Contents', var);
    end;
    /* Write SAS table row values to sheet rows */
    do while (rc ne -1);
      do row = 0 to (nobs-1);
        sheetrow=row+2;
        do col = 0 to (nvar-1);
          sheetcol=col+1;
          link col2text;
          call send (adobj, '_getProperty', 'Ranges', trim(textcol), retcell);
          if vartype(dsid, sheetcol) eq 'N' then var=put(getvarn(dsid, sheetcol), 8.);
                                            else var=getvarc(dsid, sheetcol);
          call send (retcell, '_setProperty', 'Contents', var);
        end;
        rc=fetch(dsid);
      end;
    end;
  return;
  B_CLOSE:
    /* Save and close sheet */
    call send (adobj, '_do', 'SaveAs', sheetfile, '', '1-2-3 (123)', 'False', '', 'True');
    call send (appobj, '_getProperty', 'ApplicationWindow', awobj);
    call send (awobj, '_do', 'Close', 'False');
  return;
  B_EXIT:
    /* Close 1-2-3 and application */
    call send (appobj, '_do', 'Quit');
    call send (appobj, '_term');
    call execcmd('cancel;');
  return;
  COL2TEXT:
    /* Convert SAS table column and row to 1-2-3 cell notation: e.g. "4,25" to "d25" */
    textcol=left(substr(alpha, int(sheetcol/27)+1, 1)!!
                 substr(alpha, mod(sheetcol, 27)+1, 1)!!
                 trim(left(put(sheetrow, 8.)))
                );
  return;

     Back to Main FAQ Menu
Number of visitors = Counter (since 15th May 2000)

Email: Phil Holland <phil.holland@bcs.org.uk>

Web Design by Holland Numerics

Valid HTML 4.01!