Replacing a DDE process with the SAS Add-in?

[mrtball]

Hello,

As we have moved away from using DDE to export to Excel - I'm working with a user to convert a process from using DDE to instead using the SAS Add-in for Office.

The challenges we have faced is that the user's spreadsheet has a large number of tabs, the source data is dynamic in length and we want the output excel spreadsheet to adjust according the size of the source dataset and also the variable format.  Other issues we have experienced when just mapping through using the SAS Add-in has been that the format is incorrect so the user's inbuilt Excel functions and calculations don't work.

To me, If we could copy the way DDE functions but by creating a SAS dataset instead of an Excel spreadsheet - we could then create the connection using SAS Add-In.  

So i'm just wondering does anybody have any knowledge of mirroring what is done with DDE but instead producing a pre formatted SAS data set??....as an example the DDE code that was previously used  is :

%macro DDEtable(variable_name, SASdataset, sheetname);
filename exceltab dde "excel|[&reportname..xlsm]&sheetname.!r2c1:r999c9";
data _null_;
set local.&SASdataset;
file exceltab lrecl=300 notab dlm='09'x;
put &variable_name volume &ACT_variable &EXP_variable misalignment IV efficiency delta totalvol;
run;
%mend;

%DDEtable(A_Test,out_A_Test,Another_Test);

[RW9]

I don't have Add-In, so can't answer that point.  Personally if it was me I would first look to coalesce and slim the number of applications used.  Why is Excel needed, there are reporting web based tools which can connect directly to data (Excel really isn't a great data tool).  If you have to use Excel, then why not use Excel functionality - VBA.  Dump your data out to CSV, then have a VBA script which loads its, adjusts the Excel file, applies all the necessary calculations, updates and such like.  You could also store you data in a database (SQL Server for instance) then directly access that data from both SAS and Excel.  

[thomp7050]

Like the last person to respond, I have not used the add in.  But this is the process that we use to create dozens of worksheets in a single workbook, based on results from SAS datasets.  It could perhaps be of use to you?

data _null_;
call symputx('nowyear', year(date()));
call symputx('nowmonth', month(date()));
call symputx('nowday', day(date()));
call symputx('nowhour', hour(datetime()));
call symputx('nowminute', minute(datetime()));
run;

%let myvar = %str(&nowyear)_%str(&nowmonth)_%str(&nowday)_%str(&nowhour)_%str(&nowminute); 
%let title = "F:\Main\Studies\ICO_DUALS\EnrollBuckets\Documents\EN_%str(&myvar).xlsx";

PROC EXPORT DATA=RPT_T1 outfile= &title dbms=xlsx replace;
sheet="RPT_T1_TRAN";
run;
PROC EXPORT DATA=RPT_T2 outfile= &title dbms=xlsx replace;
sheet="RPT_T2_TRAN";
run;
PROC EXPORT DATA=RPT_T2_OTHERREG outfile= &title dbms=xlsx replace;
sheet="RPT_T2_OTHERREG";
run;

Repeat the export process for however many sheets are required in the workbook.

[Reeza]

The code you've shown is a simple export to Excel Via DDE so doesn't help with any of the issues you've mentioned above. 

You may may need a combination of AddIn + Custom VBA. 

Another option is upgrade to SAS 9.4 and use ODS EXCEL. At the end of the day it really depends on the type of formatting required and where you want to invest the work.

SAS AddIn alone will not replace the full DDE functionality by any means. 

[mrtball]

Hello All,

Thank you for your responses.

The dataset's the user is creating I believe will work with the SAS Add-in, and we have managed to link up one of the original DDE source datasets with the Add-in with a bit of work involved to form the connection and format correctly in Excel. (The user has over 100 different sheets / datasets however though)

I think the thing we are struggling with is generating these multiple source datasets in the required format (the source is Character format but contains 90% numerics) which DDE would have written these to spreadsheet as. (I've managed to get the user' data to link through with the Excel 'General' format (using SAS best20. format) via the SAS Add-in - so i'm envisaging that going forward all the user would need to do is update these datasets and refresh the add-in and all linked data would then update. The only problem with this is that the user has character values in some of the headings for these cells so converting to Best20. blanks the character out as expected.

The user's Excel spreadsheets are also .xlsm (Excel macro sheet) type which i think makes the ODS Excel tricky? (We are on 9.4 M2 already.

I had wondered about using the Custom VBA script but this would need to more insight from both mine and the users side of things i think.  I will do a bit of reading around this to see if this could maybe solve our problem.

Many Thanks,

[RW9]

Well, if your already using XLSM - macro enabled Workbook, then it should be no problem to add another module in.  VBA itself is very simple and Google will have answers for anything you want to do there.  If you go down the Add-In route, that adds a layer of difficulty.  Me, I would first look at the Excel file very carefully and evaluate what if anything is actually needed from that file.  There are inumerable reporting software solutions and practices, varying degrees of complexity, and I have yet to see one I would prefer to use less than Excel.  Even something relatively simple like a database which holds your data, then your SAS, and that Excel file could both ODBC directly into one data source would limit your interactions, data storage etc.

[thomp7050]

A handy trick for your data management toolbox, regardless of what you choose: if you transpose the data, and transpose back, ALL columns in your resulting dataset will be character.  Like so:

proc transpose data=source out=results;
  var _all_;
run;

PROC SQL; 
SELECT NAME INTO :MYVAR SEPARATE BY " " FROM DICTIONARY.COLUMNS WHERE MEMNAME = "RESULTS" AND NAME CONTAINS "COL";
QUIT;
 

proc transpose data=middle out=RESULTS_FORM (drop=_name_);
var &MYVAR;
run;