Date:         Thu, 6 Apr 2000 19:01:59 -0400
Reply-To:     David.Beede@MAIL.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "David N. Beede" <David.Beede@MAIL.DOC.GOV>
Subject:      SAS to Excel via DDE problem
Content-type: text/plain; charset=us-ascii

I recently taught myself how to use DDE, drawing heavily from the extremely
helpful messages
people have posted to this list.  However..., I keep getting error messages
saying that SAS
cannot access Excel|System if I include the lines of code marked with a # below;
 if I take
them out, the macro works fine.  Does anyone have an idea about what I am doing
wrong?
TIA.

%macro genex;
%do i=1 %to &agencnum;

data _null_;
 filenam1=
  trim(left("&&agency&i"))||' '||trim(left(put("&sysdate"d,worddate.)))||'
'||trim(left('CO Report'));
 call symput('filenam1',filenam1);
 filenam2=
  trim(left("&&agency&i"))||' '||trim(left(put("&sysdate"d,worddate.)))||'
'||trim(left('Agency Report'));
 call symput('filenam2',filenam2);

run;

******************Contract office-level
reports************************************;
data a;
 retain  agency aname ocode coffadd1 coffadd2 coffcity coffstat total verif
losers totverif nonverif
  pctverif pctloser;
 set j(keep= agency aname ocode coffadd1 coffadd2 coffcity coffstat total verif
nonverif pctverif
  losers pctloser totverif)
   ;
 where agency="&&agency&i" and ocode ne 'TOTAL';
run;

data _null_;
 set a nobs=x;
 y=x+1;
 call symput('rowcoun1',trim(left(put(y,8.))));
run;

filename b dde 'Excel|[shell.xls]Sheet1!r1c1:r1c14' notab;
filename c dde "Excel|[shell.xls]Sheet1!r2c1:r&rowcoun1.c14" notab;
filename commands dde "Excel|system";

data _null_;
 file commands;
# put '[ERROR(FALSE)]';
 put '[NEW(1)]';
 put '[SAVE.AS("d:\survey reports\shell.xls",1,"",FALSE,"",FALSE)]';
run;

data _null_;
 file b;
 put 'department' '09'x 'agency' '09'x 'office code' '09'x 'address line 1'
'09'x
     'address line 2' '09'x 'city' '09'x 'state' '09'x
  'total contracts' '09'x 'verified no losers' '09'x 'verified with losers'
'09'x
  'all verified' '09'x 'nonverified' '09'x
  '(all verified)/total' '09'x 'losers/(all verified)';
run;

data _null_;
 set a;
 file c;
 put agency '09'x aname '09'x ocode '09'x coffadd1 '09'x coffadd2 '09'x coffcity
 '09'x coffstat
 '09'x total '09'x verif '09'x losers '09'x totverif '09'x nonverif '09'x
pctverif 5.1
 '09'x pctloser 5.1;
* put '!DDE_FLUSH';
run;

data _null_;
 file commands;
 length excelcmd $200.;
# put '[ERROR(FALSE)]';
# put '[SELECT("r1c3:r&rowcoun1:c3","r1c1")]';
# put '[FORMAT.NUMBER("00000")]';
# put '[COLUMN.WIDTH("","r1c1:r&rowcoun1:c14","",3,"")]';
 excelcmd='[SAVE.AS("'||trim("d:\survey
reports\&filenam1")||'.xls",1,"",FALSE,"",FALSE)]';
 put excelcmd;
run;

******************Agency-level reports************************************;
data aa;
 retain  agency aname total verif losers totverif nonverif
  pctverif pctloser;
 set jjj(keep= agency aname total verif nonverif pctverif
  losers pctloser totverif)
   ;
 where agency="&&agency&i";
run;

data _null_;
 set aa nobs=x;
 y=x+1;
 call symput('rowcoun2',trim(left(put(y,8.))));
run;

filename bb dde 'Excel|[shell.xls]Sheet1!r1c1:r1c9' notab;
filename cc dde "Excel|[shell.xls]Sheet1!r2c1:r&rowcoun2.c9" notab;
filename commands dde "Excel|system";

data _null_;
 file commands;
# put '[ERROR(FALSE)]';
 put '[NEW(1)]';
 put '[SAVE.AS("d:\survey reports\shell.xls",1,"",FALSE,"",FALSE)]';
run;

data _null_;
 file bb;
 put 'department' '09'x 'agency' '09'x
  'total contracts' '09'x 'verified no losers' '09'x 'verified with losers'
'09'x
  'all verified' '09'x 'nonverified' '09'x
  '(all verified)/total' '09'x 'losers/(all verified)';
run;

data _null_;
 set aa;
 file cc;
 put agency '09'x aname '09'x total '09'x verif '09'x losers '09'x totverif
 '09'x nonverif '09'x pctverif 5.1 '09'x pctloser 5.1;
* put '!DDE_FLUSH';
run;

data _null_;
 file commands;
 length excelcmd $200.;
 excelcmd='[SAVE.AS("'||trim("d:\survey
reports\&filenam2")||'.xls",1,"",FALSE,"",FALSE)]';
# put '[ERROR(FALSE)]';
# put '[COLUMN.WIDTH("","r1c1:r&rowcoun2:c9","",3,"")]';
 put excelcmd;
run;

%end;

%mend genex;

%genex


David N. Beede
Economist
Office of Policy Development
Economics and Statistics Administration
U.S. Department of Commerce
Room 4858 HCHB
14th Street and Pennsylvania Avenue, N.W.
Washington, DC  20230
Voice:  202.482.1226
Fax:    202.482.0325
e-mail:  david.beede@mail.doc.gov