Date:         Wed, 16 Nov 2005 13:25:02 -0500
Reply-To:     Ran S <raan67@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ran S <raan67@YAHOO.COM>
Subject:      Help with a macro, DDE

Hi,

In my dataset I have groupname, and other sum_.. variables. I want to
create a macro to write directly to excel by groupname.

Under groupname (called super6) I have groups like..Ariel, Barby, Chuck....
And now I want to set up macro so that it looks for the macro variable
(groupnames from the dataset), and for each group it automatically creates
a excel file for each group and open up excel file and dumps data for each
of the groups in different spreadsheets.  I tried to do something like
this. Please see the code below. But I know this is not right. How can I do
this? I would appreciate your help.
Thanks!


****************************************************
OPTIONS NOXWAIT NOXSYNC SYMBOLGEN;

*Invoke Microsoft Excel;
X 'D:\PROGRA~1\MICROS~1\OFFICE11\EXCEL.EXE';

DATA _NULL_;
   RC = SLEEP(5);
RUN;

FILENAME CMDEXCEL DDE 'EXCEL|SYSTEM';


%MACRO PRINTIT (GRP);

*OPEN PPM_RPT.XLS REPORT;
DATA _NULL_;
        FILE CMDEXCEL;
        PUT '[OPEN("C:\SAS\CMS"&GRP".XLS")]'; /*CHANGE*/
RUN;


DATA ALL;
 SET ALL;
 IF SUPER6=(&GRP);
RUN;

FILENAME DD01 DDE 'EXCEL|999!R12C1:R23C1' NOTAB;
FILENAME DD02 DDE 'EXCEL|999!R12C2:R23C2' NOTAB;
FILENAME DD03 DDE 'EXCEL|999!R12C3:R23C3' NOTAB;
FILENAME DD04 DDE 'EXCEL|999!R12C4:R23C4' NOTAB;
FILENAME DD05 DDE 'EXCEL|999!R12C5:R23C5' NOTAB;
FILENAME DD06 DDE 'EXCEL|999!R12C6:R23C6' NOTAB;
FILENAME DD07 DDE 'EXCEL|999!R12C7:R23C7' NOTAB;
FILENAME DD08 DDE 'EXCEL|999!R12C8:R23C8' NOTAB;
FILENAME DD09 DDE 'EXCEL|999!R12C9:R23C9' NOTAB;
FILENAME DD010 DDE 'EXCEL|999!R12C10:R23C10' NOTAB;
FILENAME DD011 DDE 'EXCEL|999!R12C11:R23C11' NOTAB;
FILENAME DD012 DDE 'EXCEL|999!R12C12:R23C12' NOTAB;

DATA _NULL_;
        SET ALL;
  FILE DD01; PUT YEAR;
  FILE DD02; PUT MONTH;
  FILE DD03; PUT SUM_M;
  FILE DD04; PUT SUM_S;
  FILE DD05; PUT SUM_C;
  FILE DD06; PUT SUM_CH;
  FILE DD07; PUT SUM_CHI;
  FILE DD08; PUT SUM_F;
  FILE DD09; PUT SUM_PR;
  FILE DD010; PUT SUM_CAP;
  FILE DD011; PUT SUM_APP;
  FILE DD012; PUT SUM_AMT;
RUN;

%MEND PRINTIT;

%PRINTIT (ARIEL);

FILENAME CMDS DDE 'EXCEL|SYSTEM';

DATA _NULL_;
        FILE CMDS;
        PUT '[SAVE()]';
        PUT '[QUIT()]';
RUN;