07-11-2014 02:16 PM
I am using the DDE method to populate an EXCEL report template.
I have a template that is a matrix of data, but all the data that needs to be populated is not consecutive. There are some calculated fields in between. I use multiple '09'x to to change to the appropriate column. This however deletes formulas that are already present in the template.
My SAS data looks like
and the report needs to look like:
|Week||Customers||Visits||Visits / Customers||Sales||Sales / Customer|
where "Visits / Customer" and "Sales / Customer " are present in the report template as formulas.
When I try skipping over the column Visits/ Customer by using multiple '09x' statements, the formula is that column is being deleted.
my SAS code looks like this.
put Week '09x'
customer '09x' '09x'
sales is being poulated in the proper column but the formula for VISITS/ CUSTOMER is being deleted.
Changing the template is not an option at this point, otherwise I would just calculate the folrmulas in my data set and write the values in EXCEL.
Does anyone have any suggestions.
07-11-2014 02:30 PM
If you can change the template (not the format) I suggest adding in a hidden sheet that you export to excel, using a standard export. Then have the cells linked to the exported version. When you export a new data set the links will remain and update automatically.
If you absolutely cannot change the template then you'll have to manually navigate to each cell you want to put numbers in and then put the number into the cell rather than a generic put statement.
07-14-2014 04:15 AM
I second Reeza's approach. If you absolutely have to do it this way, then get all your data, calculations done and export to excel with basic proc export. Have a intermediary Excel with some VBA code which will populate the template anyway you like with the data you have exported. DDE may not be supported much longer.
One further option you may want to consider is to move your template to XLSX format. This is the latest MS Office XML based format. Once you understand how it fits together (for instance rename an XLSX to .zip and then you can open and see folders/files beneath), you can pretty much build it to look however you like, as long as you know XML, and Open Office format.
07-11-2014 03:21 PM
Rather than using DDE, download and use the macro you can download at:
The process is described in the paper but, basically, what will accomplish the task is treating it as a two-step approach:
First, creating a new workbook, using the template, writing the data to the cell range that you want, but only exporting week, customers and visits.
Then, adding to the new workbook at the correct cell location and only adding sales.
For example, given the following data:
input Week Customers Visits Sales;
1 100 120 1000
2 200 130 1500
3 300 140 1600
4 400 160 1700
5 500 170 1800
and assuming that you had a template, c:\BMI_Template.xltx, with a Sheet labeled 'Sheet1', the following would use the above data (work.test) to create a new workbook (c:\BMI.xlsx), with a sheet labeled 'Want'
%exportxl(data=test (keep=Week Customers Visits),
07-13-2014 12:14 PM
I can think of two DDE approaches.
1. Create 2 or more triplets corresponding to the nonadjacent grids to be populated. In this example, one triplet would handle columns 1 through 3 and the other would do column 5.
2. Abandon triplets and instead use the DDE System channel to pass Excel formulas. One at a time change the active cell and populate it, using Excel functions.
07-15-2014 01:14 PM
Thanks all for the input. There are too many cells to update so creating a new table to link to is impractical and I don't know VBA.
I was able to change the template, but am now facing another issue.
The values are written to the correct cells. The first column is being written as a number, the reaming cells are being populated as text. The original template has all cells as numeric.
Does anyone know why this may happen.
07-15-2014 02:05 PM
I am afraid I am going to say it again, ods tagset, makes it all so simple. Have a look at this paper: http://support.sas.com/resources/papers/proceedings13/316-2013.pdf
You can set the format of any cell with the tagattr. For dde, you could have a look at: http://analytics.ncsu.edu/sesug/2004/DP03-Beal.pdf
06-24-2015 11:21 PM
06-25-2015 01:42 AM
DDE Is very old (30 years) and should better be avoided. Neither Sas or ms are promoting the usage it is only still there because of supporting all those old things. It is slow only for small data and suffering from language nls effects.
06-25-2015 04:31 PM
I don't see where you are using DDE but with it you can use the 'select' command to navigate to specific rows and columns. Instead of the second tab command you can have DDE position to the correct cell to put the value for sales.