SAS DDE not formatting output correctly in Excel

0
votes

I'm just looking to export a SAS dataset into a pre-made Excel template.

First 6 variables of my dataset (which is a .wpd file) look like:

StartDate     EndDate    product_code   Description               Leaflet    Media
04-Jul-13    07-Jul-13    256554    BUTCHER BEEF 1PK (1 KGM)       54x10        3

I currently have:

options noxwait noxsync;
x '"c:\Template.xls"'; /* <--excel template to use*/
filename template dde 'excel|Leaflets!r6c1:r183c67';   /*put data in rows 3 to 183 in leaflets sheet*/


data LEAF.results; set LEAF.results;
file template ;
     put StartDate   EndDate   product_code   Description   Leaflet  Media  
         /*and the remaining 61 variables*/    
run;

The DDE procedure works and opens the excel sheet, but the data is not formatted correctly in excel and looks like this:

StartDate      EndDate      Product code   Description  Leaflet      Media
04 July 2013   07 July 2013    256554      BUTCHER         BEEF       1PK

As you can see it seems to have treated spaces as delimiters but I'm not sure of the syntax to change this - might also be worth noting that I have 67 variables in my actual dataset so didn't want to have to informat and format them all individually.

Also, is there a way to output this dataset into my excel template and then save the template as a different filename elsewhere on my c drive?

Thanks!

2
sasexport-to-exceldde

2 Answers

2
votes

After trying every DDE option under the sun I finally stumbled across LRECL.

So, 

options noxwait noxsync;
x '"c:\Template.xls"'; /* <--excel template to use*/
filename template dde 'excel|Leaflets!r6c1:r183c67' notab **LRECL=3000**;   /*put data in rows 6 to 183 in leaflets sheet*/


data LEAF.results; set LEAF.results;
file template ;
     put StartDate   EndDate   product_code   Description   Leaflet  Media  
         /*and the remaining 61 variables*/    
run;

I'm guessing the default length of characters allowed in each cell was too short, so increasing the length allowed means each cell doesn't get split into multiple cells?

source: http://support.sas.com/resources/papers/proceedings11/003-2011.pdf

1
votes

Try changing file template ; to use a delimiter of tab, ie, file template dlm='09'x;;

Also, in the filename, add 'notab': filename template dde 'excel|Leaflets!r6c1:r183c67' notab;