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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.