topic Re: DDE - Populating an EXCEL Templage in ODS and Base Reporting

DDE - Populating an EXCEL Templage

BMI0776 — Fri, 11 Jul 2014 18:16:09 GMT

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

Week	Customers	Visits	Sales
1	100	120	1000

and the report needs to look like:

Week	Customers	Visits	Visits / Customers	Sales	Sales / Customer
1	100	120	1.2	1000	10

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.

DATA _NULL_

put Week '09x'

customer '09x' '09x'

sales;

run;

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.

Thanks,

Re: DDE - Populating an EXCEL Templage

Reeza — Fri, 11 Jul 2014 18:30:20 GMT

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.

Re: DDE - Populating an EXCEL Templage

art297 — Fri, 11 Jul 2014 19:21:18 GMT

Rather than using DDE, download and use the macro you can download at:

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

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:

data test;

input Week Customers Visits Sales;

cards;

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),

template=c:\BMI_Template.xltx,

templatesheet=Sheet1,

outfile=c:\BMI.xlsx,

sheet=Want,

type=N,

usenames=N,

range=A2)

%exportxl(data=test (keep=Sales),

outfile=c:\BMI.xlsx,

sheet=Want,

type=M,

usenames=N,

range=E2)

Re: DDE - Populating an EXCEL Templage

Howles — Sun, 13 Jul 2014 16:14:03 GMT

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.

Re: DDE - Populating an EXCEL Templage

Vish33 — Mon, 14 Jul 2014 07:24:35 GMT

I am not fully aware of DDE approach,but normally i use ODS and proc report. with in that I use computed columns where you can create these calculated columns.

Re: DDE - Populating an EXCEL Templage

RW9 — Mon, 14 Jul 2014 08:15:52 GMT

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.

Re: DDE - Populating an EXCEL Templage

BMI0776 — Tue, 15 Jul 2014 17:14:00 GMT

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.

Thanks,

Re: DDE - Populating an EXCEL Templage

RW9 — Tue, 15 Jul 2014 18:05:08 GMT

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

Re: DDE - Populating an EXCEL Templage

longwest — Thu, 25 Jun 2015 03:21:44 GMT

DDE is new for me, I don't know how to use DDE populate your matrix of data EXCEL template and sorry that I can't help you. And I want to ask how to create a matrix of data? Hope you can give me some advices.

Re: DDE - Populating an EXCEL Templage

jakarman — Thu, 25 Jun 2015 05:42:58 GMT

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.

Re: DDE - Populating an EXCEL Templage

longwest — Thu, 25 Jun 2015 06:47:28 GMT

Thank you so much for the information.

Re: DDE - Populating an EXCEL Templage

Reeza — Thu, 25 Jun 2015 15:53:22 GMT

How are you writing the data to the cells?

Re: DDE - Populating an EXCEL Templage

Hoffy — Thu, 25 Jun 2015 20:31:03 GMT

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.

Re: DDE - Populating an EXCEL Templage

Bungeoura — Fri, 26 Jun 2015 10:48:35 GMT

Is it adding in spaces that cause it to convert to text? Perhaps adding in +(-1) before the '09'x might help:

PUT sales +(-1) '09'x;