Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Regular Contributor
Posts: 156
Accepted Solution

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

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.


Accepted Solutions
Solution
‎03-26-2012 04:58 PM
Regular Contributor
Regular Contributor
Posts: 156

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

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. 

View solution in original post


All Replies
Solution
‎03-26-2012 04:58 PM
Regular Contributor
Regular Contributor
Posts: 156

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

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. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 380 views
  • 0 likes
  • 1 in conversation