Hello
I have a process where I export data from SAS into an Excel workbook using DDE. However, I also have to manipulate existing values within the target excel file. My attempts at a simple copy and paste of existing values in the worksheet are not working as expected, if anyone has done this previously please could you point me in the right direction?
Thanks
my code:
* copy and paste previous snapshots;
filename ongcopy dde "excel|Sheet1! r2c1:r11c14";
data _null_;
put '[error(false)]';
file ongcopy notab lrecl=500;
put '[select("r2c1:r11c1")]';
put '[copy()]';
put '[SELECT("r2c4")]';
put '[PASTE()]';
run;
I can't attach an excel file, but here's what the cells look like if you open Excel and paste these into Sheet1
copy from here | paste to here | |||
Aug-15 | ||||
1 | ||||
25 | ||||
0 | ||||
0 | ||||
1 | ||||
27 | ||||
18 | ||||
67% | ||||
26/05/2015 | ||||
It's been a while since I've used DDE, but your code "almost" works as is. With a couple of minor changes, it will do exactly what you intended.
From your code I'm assuming that you have already opened the workbook, in Excel, prior to running the code. If so, then simply run:
* copy and paste previous snapshots;
options noxsync noxwait;
filename sas2xl dde 'excel|system';
data _null_;
file sas2xl;
run;
data _null_;
file sas2xl;
put '[workbook.activate("Sheet1")]';
put '[select("r2c1:r11c1")]';
put '[copy()]';
put '[SELECT("r2c4")]';
put '[PASTE()]';
run;
If you want to do anything more complex, do a Google search for any of Koen Vyverman's papers. One example of his excellent papers on the topic is: http://www2.sas.com/proceedings/sugi26/p011-26.pdf
Hi,
The simplest answer to this is don't. DDE is old technology, its doesn't have full functionality beyond the scope it was initially used for. I would read up on ods tagsets.excelxp or excel. These have far more functionality in generating XML which is either native to Excel, or can be read by Excel.
If there is something outside the scope of the tagsets, maybe you have a complicated template Excel file the data needs to go over to, then re-think the approach you take. Insteand of writing to the Excel file from SAS, just drop the data out to a CSV file from SAS. Now in your Excel file write a small bit of VBA macro to read in the CSV file and process it. This option gives you all the functionality of Excel, i.e. you can read the data, create graphs, update pivots, create links etc. but you need to know VBA.
It's been a while since I've used DDE, but your code "almost" works as is. With a couple of minor changes, it will do exactly what you intended.
From your code I'm assuming that you have already opened the workbook, in Excel, prior to running the code. If so, then simply run:
* copy and paste previous snapshots;
options noxsync noxwait;
filename sas2xl dde 'excel|system';
data _null_;
file sas2xl;
run;
data _null_;
file sas2xl;
put '[workbook.activate("Sheet1")]';
put '[select("r2c1:r11c1")]';
put '[copy()]';
put '[SELECT("r2c4")]';
put '[PASTE()]';
run;
If you want to do anything more complex, do a Google search for any of Koen Vyverman's papers. One example of his excellent papers on the topic is: http://www2.sas.com/proceedings/sugi26/p011-26.pdf
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.