topic Re: SAS EXCEL DDE in SAS Enterprise Guide

SAS EXCEL DDE

amar8580 — Wed, 08 Mar 2017 12:45:26 GMT

hi all

i am new to sas

we have our sas servers on Linux.

is it possible for me using sas enterprise guide to use DDE funtionality

Re: SAS EXCEL DDE

RW9 — Wed, 08 Mar 2017 12:52:14 GMT

Firstly, DDE requires Excel to be installed on the machine, as Excel is not on Linux then no.

Secondly, DDE is decades old and in some setups doesn't work at all - like yours where your on a different OS.

Now, depends what you use the output for. If it is for data transfer, use an open source text based format - CSV, XML, which can be opened by various applications, and is cross platform and robust. If it is for review purposes, then you have several options, and you can still create Excel files:

https://communities.sas.com/t5/Base-SAS-Programming/Read-an-Excel-File-in-Linux/td-p/80822

Re: SAS EXCEL DDE

amar8580 — Wed, 08 Mar 2017 13:00:17 GMT

thanks for the reply

basically i have a xlsm file that has macro in it

i want to use sas to run that macro

convert that xlsm file in both xlsx and pdf

and then email it out to users

Re: SAS EXCEL DDE

Kurt_Bremser — Wed, 08 Mar 2017 13:18:21 GMT

A Excel macro needs Excel to be run. Excel is not available on your SAS server, so forget about it.

Export the raw data from Excel into a sensible data format, read that into SAS, and implement the logic from the Excel macro there. That also solves the problem that Excel macros do not adhere to reporting/documentation standards as required by (international) laws and regulations.

Re: SAS EXCEL DDE

ChrisHemedinger — Wed, 08 Mar 2017 13:11:52 GMT

You won't be able to use SAS on Linux to drive this. If you're using SAS Enterprise Guide, you might be able to drive some of this in a process flow. You can transfer files to/from Linux with the Copy Files task (in Tools->Data in EG 7.13, available as a custom task in earlier versions). You can run Windows commands (including scripts to automate Excel) by using the System Command task (a custom task).

Re: SAS EXCEL DDE

RW9 — Wed, 08 Mar 2017 13:47:52 GMT

I see you already have some answers, i.e. no you can't do this on Linux. Just to add to that, I assume the file will be opened on Windows at some point as this is a review file? If so why not output your SAS data to CSV and send that with the file. You macro Excel file can then open the CSV datafile and process it into the spreadsheet using an OnOpen() function.

At the end of the day Excel isn't a good medium, its not cross platform. You can create PDF directly from SAS. And create Excel files but only on Windows, but as mentioned you can create XML files which are cross platform and can be read by Excel.

Re: SAS EXCEL DDE

rogerjdeangelis — Wed, 08 Mar 2017 14:39:58 GMT

If you are familiar with R or python

Here is a related post where I read an XLSM excel workpbook and appen some data to a worksheet.You don't need SAS Access to PC-Files. The free WPS express allows you to export un limited R dataframes or matrices to SAS datasets.

/* T0099650 Appending data to an Excel XLSM workbook

SAS Forum: 

Cross posted from SAS-L
You can find a lot of information on dealing with foreign files on SAS-L

http://goo.gl/9OzDmv
https://communities.sas.com/t5/Base-SAS-Programming/export-large-datasets-to-xlsm-file/m-p/294316

HAVE (XLSM formatted Excel file)

1
2
3


SHEET1

WANT (Append 4)

HAVE (XLSM formatted Excel file)

1
2
3
4

SHEET1

SOLUTION

* It might be better to write to another xlsm workbook or
  to write to a temp XLSM and rename it. Unless yoour code is solid
  it is easy to corrupt your original XLSM;

%utl_submit_py64(%nrbquote(
from openpyxl import Workbook;
from openpyxl import load_workbook;
wb = load_workbook(filename='d:/xls/utl_xlsm_sample.xlsm', read_only=False, keep_vba=True);
/* grab the active worksheet */
ws = wb.active;

/* Data can be assigned directly to cells */
ws['A4'] = 88;

wb.save('d:/xls/utl_xlsm_sample.xlsm');
));

SAS MACRO


%macro utl_submit_py64(pgm)/des="Semi colon separated set of py commands";
  * write the program to a temporary file;
  filename py_pgm "%sysfunc(pathname(work))/py_pgm.py" lrecl=32766 recfm=v;
  data _null_;
    length pgm  $32755 cmd $255;
    file py_pgm ;
    pgm="&pgm";
    semi=countc(pgm,';');
      do idx=1 to semi;
        cmd=cats(scan(pgm,idx,';'));
        if cmd=:'.' then cmd=substr(cmd,2);
        put cmd $char96.;
        putlog cmd $char96.;
      end;
  run;
  %let _loc=%sysfunc(pathname(py_pgm));
  %put &_loc;
  filename rut pipe  "C:\Python_27_64bit/python.exe &_loc";
  data _null_;
    file print;
    infile rut;
    input;
    put _infile_;
  run;
  filename rut clear;
  filename py_pgm clear;
%mend utl_submit_py64;