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 by bdi90998

VIEWS: 0 PAGES: 33

									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.

								
To top