Date:         Tue, 23 Sep 2003 08:06:52 -0700
Reply-To:     Helen <sunchunkui@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Helen <sunchunkui@HOTMAIL.COM>
Organization: http://groups.google.com/
Subject:      Re: how to change certain cells' background color conditionally
              in SAS via dde excel?
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I have figured out how to do it via SAS data step instead of
'for.cell' function in DDE Excel. But I am still interesting how to do
it via DDE. Could someone tell me the trick?

Helen

data status;
   length id date status $8;
   input id date status;
 datalines;
   1001  20011001 alive
   1002  20020301 deceased
   1003  20011123 alive
   1004  20030402 deceased
 ;
 run;


*Launch the Excel application and define a DDE system doublet;
*fileref SAS2XL pointing to it;
options noxsync noxwait xmin;
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+10;
        do while (fid le 0);
          fid=fopen('sas2xl','s');
          time=datetime();
          if (time ge stop) then fid=1;
        end;
  end;
  rc=fclose(fid);
run;


%*close the default blank workbook and create a new one with 1 empty
worksheet.;
%*Save it somewhere in order to fixate the file name.;
%*Note that Excel uses the default worksheet name SHEET1.;
data _null_;
  file sas2xl;
  put '[file.close(false)]';
  put '[new(1)]';
  put '[error(false)]';
  put '[save.as("c:\temp\color")]';
run;

%*Define a triplet-style DDE fileref status, pointing to some cells in
the first;
%*3 columns of the worksheet;
filename status dde 'excel|c:\temp\[color.xls]sheet1!r1c1:r30c3'
notab;
%let tab='09'x;

%* Then write work.status contents there;
data _null_;
  file status;
  set status;
  put id &tab
      date &tab
          status &tab;
run;
filename status clear;

%*adjust the status date column width to a best fit;
data _null_;
  file sas2xl;
  put '[column.width(0,"c2",false,3)]';
  put '[select("r1c1")]';
run;

data _null_;
  file sas2xl;
  set status nobs=recno;
  length ddecmd $100;

  *get row number;
  if status='deceased' then do;
    put '[select("r' _n_ 'c3:r' _n_ 'c3")]';
    put "[PATTERNS(1,  1, 3, true)]";
  end;

  if _n_=recno then do;
    put '[file.close(true)]';
  end;
run;

filename sas2xl clear;