Working Smarter, Not Harder with DDE: Did I Really Spend All That Time Just Formatting My Report?

1. Working Smarter, Not Harder with DDE:Did I Really Spend All That Time Just Formatting My Report? Bobby Kidd Vice President Senior Statistical Portfolio Analyst First Horizon National Corp

2. Hey, What’s With the Acronyms! • DDE stands for Dynamic Data Exchange • DDE in SAS gives you the ability to read from, write to, and issue program commands to many Microsoft Applications such as Excel and Word • Since most reporting is done with Excel we will limit ourselves to that program

3. But I Can Already Read From and Write To Excel • Proc Import and Proc Export both require the data to start at the top right corner, Cell A1 of the first sheet. • DDE, however, can read from and write to any cell or group of cells in any sheet in the file. • It can also create new rows, columns, and worksheets.

4. But, that’s not all! • In addition to the freedom of defining where you want the data • You can change the format and the font • Copy and Paste from other cells, worksheets and even other files. • Create new new formulas • Add Worksheets, Columns and Rows • In short, just about everything you can do in Excel, you can program into DDE

5. OK, But why would I want to? • The less man(ual) handling of the data, the better • Don’t have to worry about copying over data • Don’t have to remember to format those 1 or 2 cells down in the corner of the sheet. • How many times have you had to go back and fix typo’s and formatting errors • Data provided from someone else's formatted reports • Do you really enjoy copying, pasting and formatting the same report every day, week, month when you can make SAS do it all for you in seconds.

6. The Basics • Very similar to working with flat files • Starts with filename statement • DDE triplet: program|filepath[filename]sheet!location • Filename sample DDE ‘excel|c:\foo\[bar.xls]yourdatahere!R4c5’; • Read data with input statement • Write data with put statement

7. Mild filename readme dde 'excel|C:\...\SAS Pres\[samples.xls]Read Me!R5C3:R14C6'; data DDERead; infile readme; informat Name $8. Sex $1. Purchase_Amount dollar8.2 State$2.; input Name$ Sex$ Purchase_Amount State$; run; • Read Data • Be mindful of informats

8. Mild

9. Mild filename writeme1 dde 'excel|C:\...\SAS Pres\[samples.xls]Write Me!R5C2:R8C3'; filename writeme2 dde 'excel|C:\...\SAS Pres\[samples.xls]Write Me!R12C2:R13C3'; data _null_; set state; file writeme1; put StateTotal; run; data _null_; set sex; file writeme2; put Sex Total; run; • Write Data • Just like writing to flat file

10. Medium filename readme dde 'excel|C:\...\SAS Pres\[samples.xls]Read Me!R5C3:R14C6' notab; data readdde; infile readme dlm = '09'x; informat Name $8. Sex $1. Purchase_Amount dollar8.2 State$2.; input Name Sex Purchase_Amount State; run; • Increase Control • Use notab to gain control over delimiters • When reading use dlm=’09’x for the tab key • Flat file default is space delimited so “Abel C” would be two fields

11. Medium • Without dlm=’09’x and notab • With dlm=’09’x and notab

12. Medium filename MinP dde 'excel|C:\...\SAS Pres\[samples.xls]Write Me!R16C2:R16C3' notab; filename MaxP dde 'excel|C:\...\SAS Pres\[samples.xls]Write Me!R19C2:R19C3' notab; data _null_; set min; file MinP; Put Name '09'x Purchase_Amount; run; data _null_; set max; file MaxP; Put Name '09'x Purchase_Amount; run; • Control Write delimiters • Use notab to prevent SAS from changing spaces into tabs • Use ’09’x to place tab delimiters where you want them.

13. Medium • Without notab, with ’09’x: spaces become tabs • With notab, without ’09’x: everything is one field

14. Hot • Beyond Data: Formatting, formulas, files • It’s not a virus: Macrofun.exe • Available from Microsoft • Macro4 helpfile • Contains most if not all Excel macro4 functions • Filename cmds dde 'excel|system'; • Allows SAS to issue system commands • Open, save, and close files, etc.

15. Hot • Macro Functions • Example Format.font • Function Name and syntax given in detail. • Parameter descriptions and values are documented • Only need to specify parameters as far as you need • Use with Put statement: put '[format.font("script",,1)]';

16. Hot • Excel must be opened first • Manual • Easy but tedious • X command • Issues DOS commands • Noxwait, noxsync allow SAS to run concurrently with Excel • Doesn’t like spaces • Use “cd” to reach folders with spaces in names

17. Hot • Start Excel • Options noxwait noxsync; • x 'cd c:\Program files\microsoft office'; • x 'Office10\excel.exe'; • Open file • Use sleep function to ensure excel has loaded before issuing commands. • Commands still issued with put statements • filename cmds dde 'excel|system'; data _null_; file cmds; put '[file-open("C:\...\SAS Pres\sample.xls")]'; run;

18. Hot • Implementation filename cmds dde 'excel|system'; data _null_; data _null_; file cmds; file cmds; put '[select("r5c2:r8c2")]'; put ‘[select(“r5c3:r8c3”)]’; put '[format.font(“times new roman",,1)]'; put ‘[format.number(“$#,###.00”)]; run; run;

19. Hot • Save, close and Quit • Save.as() to save file • File-close() to close the wookbook • Quit() to end excel • Commands still issued with put statements • data _null_; file cmds; put '[save.as("C:\...\Sas Pres\sample.xls")]'; put '[file-close()]'; put '[quit()]'; run;

20. As Hot As You Can Take • Write formulas: • put ‘[formula("=sum(R[-19]C:R[-14]C)/R38C20")]’; • put ‘[formula("=sum(R12C20:R17C20)/R38C20")]’; • Combine with ODS html output into .xls • Use activate(filename) and workbook.activate(sheet) to choose between file sheet • Use select(cellrange), copy(), paste() • Combine with Macro Variables • Automatically change rows, columns with month • Define columns as function of month • Macros only resolve inside double quotes • put "[formula(""=sum(R[-19]C:R[-14]C)/R38C&col."")]";

21. Complete Automation • Read, Write and Manipulate Excel all from within SAS • With use of SAS’ wakeup function can schedule reports to be ready when you show up to work • Hit F8 and walk away

22. Questions