The SAS Output Delivery System and reporting techniques

DDE - Populating an EXCEL Templage

Reply
Occasional Contributor
Posts: 11

DDE - Populating an EXCEL Templage

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

WeekCustomersVisitsSales
11001201000



and the report needs to look like:


WeekCustomersVisitsVisits / CustomersSalesSales / Customer
11001201.2100010


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,




Super User
Posts: 19,167

Re: DDE - Populating an EXCEL Templage

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.

Super User
Super User
Posts: 7,720

Re: DDE - Populating an EXCEL Templage

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.

PROC Star
Posts: 7,437

Re: DDE - Populating an EXCEL Templage

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)

Regular Contributor
Posts: 184

Re: DDE - Populating an EXCEL Templage

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.

Frequent Contributor
Posts: 117

Re: DDE - Populating an EXCEL Templage

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.

Occasional Contributor
Posts: 11

Re: DDE - Populating an EXCEL Templage

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,

Super User
Super User
Posts: 7,720

Re: DDE - Populating an EXCEL Templage

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

Super User
Posts: 19,167

Re: DDE - Populating an EXCEL Templage

How are you writing the data to the cells?

Occasional Learner
Posts: 1

Re: DDE - Populating an EXCEL Templage

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;

Occasional Contributor
Posts: 7

Re: DDE - Populating an EXCEL Templage

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.

Valued Guide
Posts: 3,208

Re: DDE - Populating an EXCEL Templage

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.

---->-- ja karman --<-----
Occasional Contributor
Posts: 7

Re: DDE - Populating an EXCEL Templage

Thank you so much for the information.Smiley Happy

Occasional Contributor
Posts: 9

Re: DDE - Populating an EXCEL Templage

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.

Ask a Question
Discussion stats
  • 13 replies
  • 1221 views
  • 1 like
  • 10 in conversation