sas2excl macro won't start excel

paulsparrow — Thu, 15 Oct 2009 16:09:58 GMT

Below is a macro taken from a SUGI event years ago and modified some. It opens up excel and dumps in SAS data with options for various tasks.

In starting a new contract at a new site I can no longer get the macro to open up excel. It must be open before running the macro. I know somewhere you need to set a path in windows or something. It's been too long ago to remember. Anyone got any ideas?

/*******************************************************************\
| Copyright (C) 1997 by SAS Institute Inc., Cary, NC, USA. |
| |
| SAS (R) is a registered trademark of SAS Institute Inc. |
| |
| SAS Institute does not assume responsibility for the accuracy of |
| any material presented in this file. |
\*******************************************************************/

/* SAS2EXCL.sas, example from SUGI 17 proceedings page 117 */
/* will send via dde from sas to excel and retain column headings */

/* Enhancements Log - all completed by Boaz Wong (604)432-5441

1) Modified to correct errors in original code downloaded from SAS

2) Revised to invoke EXCEL97

3) Resolved truncation problems caused by extra long lines of data

4) New parameters were added to allow saving to a specific directory

5) New Capability to auto-detect whether Excel is running or not

6) More parameters added:
a) option to have no labels (use colhead=NONE)
b) specific starting point of block of cells to insert into
c) sortby parameter - order that variables will be inserted

7) Multiple pathnames are searched for Excel differences between
Win95 and Win98 installations

😎 Updated LRECL of Excel file to 2048 - October 25, 2000

9) Added code to remove trailing blank

10) Updated LRECL of Excel file to 2560 - July 5, 2001

11) Updated LRECL of Excel file to 2816 - Jan 14, 2002
*/

options noxwait noxsync nomprint nosymbolgen;
%macro sas2excl (start=Y, /* No longer required */
close=N, /* closes excel if you desire use 'Y' to close */
new=Y, /* creates new workbook */
open=N, /* opens existing workbook */
clear=N, /* clear worksheet prior to populating */
colhead=NAME, /* change to LABEL to have columns use labels */
sasin=_last_, /* SAS dataset */
exdirin=, /* Excel directory */
excelbk=[BOOK1], /* Excel workbook - if existing-add .xls */
excelin=BOOK1, /* Excel file */
excelsh=SHEET1, /* Excel sheet */
exdirout=, /* Excel directory */
excelout=,
st_row=1, /* Starting row of Excel Block of data */
st_col=1, /* Starting column of Excel Block of data */
sortby= /* possible values: varnum, name, label */
);

%if %upcase(&colhead) ne NAME and
%upcase(&colhead) ne LABEL and
%upcase(&colhead) ne NONE %then %do;
data _null_;
error 'Invalid parameter error: COLHEAD=' "&colhead";
abort;
run;
%end;

%if %sysfunc(exist(&sasin)) %then %do;
data _null_;
if 0 then set &sasin nobs=count;
if count = 0 then do;
error 'Invalid Dataset error: ' "&sasin" ' is empty';
abort;
end;
else stop;
run;
%end;
%else %do;
data _null_;
error 'Invalid Dataset error: ' "&sasin" ' does not exist';
abort;
run;
%end;

%if %upcase(&colhead) eq NONE %then %do;
%let offset=0;
%end;
%else %do;
%let offset=1;
%end;

filename cmdexcel dde 'excel|system';

%let fid=%sysfunc(fopen(cmdexcel,S));
%if &fid gt 0 %then %do;
%let rc=%sysfunc(fclose(&fid));
%end;
%else %do;
%let expath='C:\Progra~1\Micros~2\Office\EXCEL.EXE';
%if %sysfunc(fileexist(&EXPATH)) %then %do;
x &expath;
data _null_;
x=sleep(5);
run;
%end;
%else %do;
%let expath='C:\Progra~1\Micros~1\Office\EXCEL.EXE';
%if %sysfunc(fileexist(&EXPATH)) %then %do;
x &expath;
data _null_;
x=sleep(5);
run;
%end;
%else %do;
%let expath='C:\Program Files\Microsoft Office\OFFICE11';
%if %sysfunc(fileexist(&EXPATH)) %then %do;
x &expath;
data _null_;
x=sleep(6);
run;
%end;
%else %do;
%let expath='C:\Progra~1\Msoffi~1\Office\EXCEL.EXE';
%if %sysfunc(fileexist(&EXPATH)) %then %do;
x &expath;
data _null_;
x=sleep(10);
run;
%end;
%else %do;
data _null_;
error 'Error: Excel could not be found';
abort;
run;
%end;
%end;
%end;
%end;
%end;

%if &new eq Y %then %do;
data _null_;
file cmdexcel;
put "[new]";
run;

filename cmdexcs dde 'excel|system!selection';

data _null_;
length path $ 80;
infile cmdexcs pad dsd notab dlm='09'x;
input path $ @@;
i = index(path,']');
j = index(path,'!');
workbook = substr(path,1,i);
sheet = substr(path,i+1,j-i-1);
call symput('excelbk',trim(workbook));
call symput('excelsh',trim(sheet));
run;

%end;

%if &open eq Y %then %do;
data _null_;
file cmdexcel;
put "[open(""&exdirin&excelin"")]";
run;
%end;

proc contents data=&sasin noprint
out=conts(keep=nobs NAME label format formatl formatd type varnum);
run;

%if &SORTBY ne %then %do;
proc sort data=CONTS;
by &SORTBY;
run;
%end;

data _null_;
set conts end=eof;
if label eq '' then
label=NAME;
call symput('col'||left(_n_),trim(NAME));
call symput('lab'||left(_n_),trim(LABEL));

if eof then do;
call symput('columns',trim(left(_n_)));
call symput('rows',trim(left(nobs)));
end;
run;

%if %upcase(&colhead) ne NONE %then %do;
filename excel dde "excel|&exdirin&excelbk&excelsh.!r&st_row.c&st_col:r&st_row.c%eval(&columns+&st_col)" lrecl=2816 notab;

data _null_;
file excel;
hextab='09'x;
%if %upcase(&colhead) eq NAME %then %do;
put %do i=1 %to &columns;
"%trim(&&col&i)" hextab +(-1)
%end;
;
%end;

%else %if %upcase(&colhead) eq LABEL %then %do;
put %do i=1 %to &columns;
"%trim(&&lab&i)" hextab +(-1)
%end;
;
%end;
run;
%end;

filename excel dde "excel|&exdirin&excelbk&excelsh.!r%eval(&st_row+&offset)c&st_col:R%eval(&st_row+&rows+&offset)C%eval(&st_col+&columns)" lrecl=2816 notab;

data _null_;
set &sasin;
hextab='09'x;
file excel;
put %do i=1 %to &columns;
%trim(&&col&i) +(-1) hextab +(-1)
%end;
;
run;

%if &excelout ne %then %do;
data _null_;
file cmdexcel;
put "[save.as(""&exdirout&excelout"")]";
run;
%end;

%if %upcase(&close) eq Y %then %do;
data _null_;
file cmdexcel;
put "[close]";
run;
%end;
%mend sas2excl;

Re: sas2excl macro won't start excel

LinusH — Fri, 16 Oct 2009 12:56:40 GMT

1997, it's quite some time ago?
Without reading your code in detail, my guess that this could version related (SAS and/or Excel).
You might want to check out some newer examples/papers. Maybe this one?

http://www2.sas.com/proceedings/forum2008/259-2008.pdf

/Linus

topic sas2excl macro won't start excel in SAS Programming

sas2excl macro won't start excel

Re: sas2excl macro won't start excel