read and write to Excel using DDE in UNIX

Reply
Occasional Contributor
Posts: 6

read and write to Excel using DDE in UNIX

Objective : I want to read and write to Excel using DDE.

what are all the additional components that need to be installed?

current platform - UNIX

Components installed -  SAS ANALYTICS PRO 9.1,SAS/SHARE,SAS/CONNECT,SAS/SHARE*NET ,SAS/ACCESS Interface to DB2

Occasional Contributor
Posts: 15

Re: read and write to Excel using DDE in UNIX

Hi,

I am afraid you cannot do this directly in unix, using real dde (at least not in the 9.1 version).

In stead there are other possiblilties:

-use the ods excelxptagset (there are SAS Papers on this and maintained until recent).

-use html output and give the filename the extension xls, fooling Excel then.

If you need to write to specific cells in the excel file, you can probably use the PC-files path (if that is licensed for you of course).

Regards,

Ton

Occasional Contributor
Posts: 6

Re: read and write to Excel using DDE in UNIX

Posted in reply to TonWiegman

Hi,

Thanks for the reply. We need to read from and write to specific cells in excel file. When you say PC-files do you mean SAS/ACCESS® Interface to PC Files.

If so, if we install the component SAS/ACCESS® Interface to PC Files how can we achieve the read write in excel. Can we use DDE or is there any other ways too to read / write to excel cells from Unix based SAS using SAS/ACCESS® Interface to PC Files.

Regards & thanks,

Isaac.

Occasional Contributor
Posts: 15

Re: read and write to Excel using DDE in UNIX

Hi,

Below some examples.

You need to have a pc set-up with the sas pcfiles-server on it. That pc will be also a gateway to your windows environment.

Any mapping to drives etc can be accessed, via this (look at the examples, d: is a local disc, x: is a mapping to a fileserver).

Reading is possible and writing.

I hope this helps for you.

Ton

/* Connection information in the proc import */

proc import dbms=excelcs out=work.invoice

             datafile="d:\testunix.xls"

             replace;

      server="nlvepc05826";     /* Name of PC files server    */

      port=8621;           /* Port number listening on the PC server */

      version='2002';      /* Excel file version */

      sheet="ClassRange";  /* Sheet name */

      scantext=yes;        /* Scan all rows data for the largest size */

      usedate=yes;         /* Use DATE format for date/time columns */

      scantime=yes;        /* Scan and identify time columns */

      dbsaslabel=none;     /* Leave SAS label names to be nulls */

      textsize=512;        /* Largest text size allowed */

run;

/* Proc Export */

proc export dbms=excelcs data=sashelp.class outfile="x:\testunix1.xls" replace;

   sheet=employee  ;

   version="2002"  ;     /* Excel version  */

   server="nlvepc05826" ;     /* Server name    */

   port=8621;            /* Port number    */

RUN;

proc export dbms=excelcs data=sashelp.class outfile="g:\ton\testuffsdnix2.xls" replace;

   sheet=employee  ;

   version="2002"  ;     /* Excel version  */

   server="NLVEPC07082" ;     /* Server name    */

   port=8621;            /* Port number    */

RUN;

Super User
Posts: 19,782

Re: read and write to Excel using DDE in UNIX

Can you explain why you "need" to export to specific cells?

A work around I use, is to export data to specific sheets in a pre-formated excel workbook that is then linked appropriately. This won't work if the tables/cells change over time (ie table becomes larger over time).

Occasional Contributor
Posts: 6

Re: read and write to Excel using DDE in UNIX

Thanks for the reply. Linking is an option.

The data in the excel file that need to be read will be in an unstructured format. But the cells position will not change.

Can I install a client version of SAS with the components (STATPLUS,    ENTERPRISE MDDB Server common products) in windows machine and using DDE ro read from excel and upload to unix server as sasdatasets? Will this option work ?

Occasional Contributor
Posts: 15

Re: read and write to Excel using DDE in UNIX

Hi,

These last questions are beyond my knowledge. ;-)

Super User
Posts: 19,782

Re: read and write to Excel using DDE in UNIX

I'm not familiar with those components, but if you have Base SAS installed on a windows machine and a connection to your server then yes you can.

Occasional Contributor
Posts: 6

Re: read and write to Excel using DDE in UNIX

Hi,

Thanks for all your valuable replies.

Regards & thanks,

Isaac.

Occasional Contributor
Posts: 15

Re: read and write to Excel using DDE in UNIX

Hi,

Just as an example:

There can be situations where you would write a count of records to a specific cell,

And the details would go below that.

I think your second remark highlights this.

Regards,

Ton

Occasional Contributor tlt
Occasional Contributor
Posts: 11

Re: read and write to Excel using DDE in UNIX

Hi,

DDE is Microsoft Windows specific.

excerpt from http://www2.sas.com/proceedings/sugi26/p029-26.pdf

In order for the exchange to occur, both applications need to be simultaneously running in Windows.

One of them, the “client”, must take the initiative to begin the process of exchanging data, and terminate the connection after the exchange is completed.

You should also read at usage note 20767 : Use of DDE in SAS EG or SAS Connect

HTH

PROC Star
Posts: 7,471

Re: read and write to Excel using DDE in UNIX

Is this a one-time (or so) need that can involve some manual intervention, or does it have to be a fully automated process?

Occasional Contributor
Posts: 6

Re: read and write to Excel using DDE in UNIX

Hi,

It need to be a fully automated project.

Regards & thanks,

Isaac.

Super Contributor
Posts: 644

Re: read and write to Excel using DDE in UNIX

It is possible to create a process in Base SAS running on a Windows platform that will open an instance of MS Excel on the same PC, with the target file loaded (checking first whether the file is already open).  The process can then download data from another server and populate the report using DDE, finally saving the report and closing MS Excel.  I know, because I used to do this.  From memory it was necessary to close all other instances of Excel to ensure writing to the target file.

Now I would not bother.

It is simpler to use a standard SAS method for writing the data to a hidden page in the workbook, and then use cell pointers to the data to update the visible pages.  The visible reports can be formatted to suit the client's requirements.

Richard

Trusted Advisor
Posts: 3,212

Re: read and write to Excel using DDE in UNIX

Posted in reply to RichardinOz

DDE with UNIX is out of any options. Unix is not Microsoft and DDE is a very old one of Microsoft they don not want to continue.

SAS/ACCESS Pcfiles is interesting because you can install an application/tool on Windows. SAS/ACCESS(R) 9.4 Interface to PC Files: Reference  

By that it is opening a Microsoft specials formats to the Windows platform. The access is by Libname usage that limits you to that approach.

If you want to use DDE to update specific types and cells you are on a dead end.

If you just want to create/maintain  excel-files it will work wiht access/pcfiles. You even could create XLSX (ACE) version for excel on Unix. (Chapter in the reference)

If you could work with the xlsx files and they are conforming the OASIS open-doc specs than you can read (update?) them as they are ZIP-files with a lot of XML-files in submaps.

You could try this yourself by opening up a xlsx excel-file using the unzip program. Zip access ia available in SAS 9.4

---->-- ja karman --<-----
Ask a Question
Discussion stats
  • 18 replies
  • 2644 views
  • 0 likes
  • 9 in conversation