using proc append (i think) to add to an excel file

[HB]

Hey,

I have this.  It works.

LIBNAME MYXLS "K:\hb_progs\match_excel\a_RPT_120318.XLS";

PROC DATASETS LIB=MYXLS;
DELETE 'report'n;
RUN;
QUIT;

proc sql;
create table myxls.report as
select distinct hsdis,
(select count(thing1)
from HSCLISIR
where thing1=1 and hsdis='a')
as t1,
(select count(thing2)
from HSCLISIR
where thing1=0 and hsdis='a')
as t0,

...goes on for awhile with a bunch of counts of different things...

from hsclisir

where hsdis='a';

This makes an one row multiple column Excel file that I'm trying to make into a mail merge source.  Each row will becone an entity.

Aside from the fact that I think there is a better way to do this, what I want to do right at the moment is the same proc sql but for hsdis entity b.  There are like 40 entities but that is anoter story.

What I think I want is a PROC APPEND somewhere.  I use the PROC DATASETS with the delete because if you don't use the delete and the Excel file already exists (it will, the report will be run multiple times) then SAS blows up and won't write.  What I want to do is append the info for b, then for c, etc. 

I can't see syntax that looks hopeful.

proc append base=myxls

data = what to put here???

second PROC SQL starts to make data to be appended

Maybe the second (and subsequent) PROC SQL's have to INSERT INTO instead of CREATEing???

Your gentle guidance is humbly requested.

UPDATE:

Following my own suggestion, I tried

proc sql;

INSERT INTO myxls.report ( etc etc

for the second SQL statment.

The SQL doesn't throw any errors but I get

ERROR: No Update/Append allowed. Set libname option SCAN_TEXT=NO to enable Update and Append operation.

So I added that:

LIBNAME MYXLS "K:\hb_progs\match_excel\a_RPT_120318.XLS" SCAN_TEXT=NO;

but no joy.

Hmmmmm.

[HB · Accepted solution]

HAH!!!

Miller time!

I set SCAN_TEXT=NO on the wrong LIB call.  Put it on the correct one and it works a treat.

Now it builds the Excel file row by row.   Okay.  I'll be back with another question.  I am doing what I am doing as a clunky quick fix.  I still want to do it right.