Creating Excel Reports Using Dynamic Data Exchange James Shields What is DDE? Dynamic Data Exchange(DDE) is a protocol built into Microsoft Windows that allows users to share data between applications. Methods I have used to fill out Excel Sheets. • Cut and paste. • Import delimited files created by SAS. • Import HTML files created by SAS/ODS. • Proc Export to Excel sheets. Problems Too many steps for automated reporting. Not flexible enough for dynamic reporting. Not easily changeable to accommodate formatting requests. Solution Dynamic Data Exchange with Excel • Can dump data directly into cells. • Can easily format cells (colors, fonts, outlines) • Can write formulas to cells. • Can even create and kick off macros. Sources Almost all the DDE commands used in this presentation came from two papers written by Koen Vyverman. • “Creating Custom Excel Workbooks from Base SAS with Dynamic Data Exchange: A Complete Walkthrough” SUGI 27 • “Using Dynamic Data Exchange to Export Your SAS® Data to MS Excel” SUGI 26 • http://www.vyverman.com/ Starting Excel options noxwait noxsync; filename sas2xl dde 'excel|system'; data _null_; length fid rc start stop time 8; fid=fopen('sas2xl','s'); if (fid le 0) then do; rc=system('start excel'); start=datetime(); stop=start+20; do while (fid le 0); fid=fopen('sas2xl','s'); time=datetime(); if (time ge stop) then fid=1; end; end; rc=fclose(fid); run; Opening an existing template. data _null_; file sas2xl; put '[Open("C:\Final Distributor Report DSR\Distributor Template.xls")]'; put '[error(false)]'; run; The Template Distributor: Evaluation Period Previous Period Delta Total IDEXX Sales $ - $ - $ - Product New to IDEXX Lost from IDEXX Slides All Slides excl. 12 packs $0 $0 $0 Writing to an Excel File filename recrange dde 'excel|[Distributor Template.xls]Sheet1!r3c2:r3c2' notab; data _null_; file recrange; put "&Distributor"; run; “Distributor” Loop. proc freq data=in.dm_trans noprint; table distributor /out=distlist; run; data _null_; set distlist end=final; call symput(compress('dist'||_N_),distributor); if final then call symput('numobs',_N_); run; %do i=1 %to &numobs; %number_crunch(&&dist&i,&start1,&start2,&per_months); %end; Data Dump filename recrange dde 'excel|[Distributor Template.xls]Sheet2!r1c1:r15c12' notab; data _null_; set &summary_data; file recrange dlm='09'x; if _n_ = 1 then do; put 'class' '09'x 'period1_class_total' '09'x 'class_total_new' '09'x 'class_total_lost' '09'x 'dist_old_delta' '09'x 'dist_new_delta' '09'x 'dist_dft_delta' '09'x 'period2_class_total' '09'x ; end; put class period1_class_total class_total_new class_total_lost dist_old_delta dist_new_delta dist_dft_delta period2_class_total ; run; Saving the File %let directory=C:\Final Distributor Report\y2003 2002 dist report\; %let filepath=%str(%'[save.as("&directory.&distributor.&second_ period_title")]%'); data _null_; file sas2xl; put %unquote(&filepath); put '[file.close(false)]'; run; Formats proc format; value $disttype 'total_lost' = "Lost To IDEXX" 'total_new' = "New to IDEXX" 'new' = "SS - New to REP" 'stayed' = "SS - Stayed with Rep" 'defect' = "SS - Defect from Rep" ; run; proc datasets library=work nolist; modify Feline_Combo; format dist_type $disttype.; quit; Renaming a sheet data _null_; file sas2xl; put '[workbook.next()]'; put '[workbook.insert(3)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab; data _null_; file xlmacro; put '=workbook.name("sheet3","Feline Combo")'; put '=halt(true)'; put '!dde_flush'; file sas2xl; put '[run("macro1!r1c1")]'; run; Changing Fonts Put '[format.font("Courier",200,true,false,false, fa lse,3,false,false)]'; “Font Name”, Font Size, bold,italic,underline, strike-through,color, outline,shadow Moving a sheet %let workbook=%str(%'[workbook.move("Feline Combo","&distributor.&second_period_title..xls", 4)]%'); data _null_; file sas2xl; put %unquote(&workbook); run; Cleaning Up data _NULL_; file sas2xl; put '[workbook.delete("macro1")]'; put '[workbook.activate("Summary")]'; put '[workbook.hide("sheet2")]'; run; Now for the Demo What are my sources? I’m not a good programmer. But I know how to find the right information. I heard there was a great DDE paper presented by a SAS user from Luxembourg. How did I find his papers? Luxembourg is a small country. My Primary Source Web Searches Bound to produce PDF’s from SUGI and Regional SUG conferences. Group Searches What we used to call Newgroups. The SAS Group is “comp.soft-sys.sas”. - Searchable. - Very Active Group. Other Sources • www.sas.com (customer support) • www.nesug.org (proceedings) • Other regional SAS user groups • www.sconsig.com - Job Related Info - Tips and Techniques - And More SUGI Proceedings http://www.lexjansen.com/sugi/ - papers archived to 1997 - Easily to Search SUGME News WWW.SUGME.ORG Steering Committee Steering Committee • James Shields – President • Prashant Mittal – Liaison • Treasurer – Leslie J. Somos • Refreshment – Deb Thayer • At Large – Mira Shapiro • At Large – Jennifer Canfield • Secretary - OPEN Membership Changes Membership for January through June is now only $15. But we also ask for $10 from non- members when attending meetings. So why not join? How about Presenting? • We plan on picking up the pace of meetings. But to do that we would like to find more local speakers. • That means we need your help. - Membership is free if you present. • We also need meeting places. What I plan on presenting. Address matching routines using SAS. Time Series Forcasting using SAS. Importing SAS data into R. What do you want to see presented? Next Meeting April 2’nd at IDEXX We plan on teaching a basic SAS class. More details to follow. Do you want to help? Upcoming Events SUGI in Montreal, May 9 – 12, 2004 • NESUG in Baltimore, Nov 14 – 17, 2004 - Get your papers ready and use SUGME as a dry run.
Pages to are hidden for
"Creating Excel Reports Using Dynamic Data Exchange James Shields What is DDE Dynamic Data Exchange DDE is a protocol built into Microsoft Windows that allows users"Please download to view full document