Moving along.
I have this block of code that generates a nice Excel listing file.
LIBNAME MYXLS2 "K:\place_where_files_go\hb\entity.xls";
PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;
proc sql;
create table myxls2.data as
select fieldname1, fieldname2, fieldname3, fieldname4
from table
where fieldname5='entity';
This gets repeated 44 times- one time for each entity for which an output file is generated. There are 44 entities.
What I would like is something like:
list = some sort of list (array?) of entities
for each entity in list
LIBNAME MYXLS2 "K:\place_where_files_go\hb\<a variable that changes each time through the loop and is the entity>.xls";
PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;
proc sql;
create table myxls2.data as
select fieldname1, fieldname2, fieldname3, fieldname4
from table
where fieldname5='<the entity variable again>';
next entity
so that I don't have to repeat the code block a gazillion times.
Is such a thing possible?
Thy this one. (not tested)
%let names=fred jane george;
%macro test;
%do i=1 %to %sysfunc(countw(&names));
%let var=%sysfunc(scan(&names,&i));
LIBNAME MYXLS2 "K:\place_where_files_go\hb\&var..xls";
PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;
proc sql;
create table myxls2.data as
select fieldname1, fieldname2, fieldname3, fieldname4
from table ???
where upcase(fieldname5)="%upcase(&var)";
quit;
libname MYXLS2 clear;
%end;
%mend;
%test
you only need to change the highlighted part?
where fieldname5='<the entity variable again>';
No the code block generating the excel file would need to change in 2 places.
One- the name of the file outputted. First time through the loop it might be fred.xls, second time through jane.xls, third time through george.xls
K:\place_where_files_go\hb\<a variable that changes each time through the loop and is the entity>.xls";
Two the SQL woudl change. Where fieldname5="fred", Where fieldname5="jane", Where fieldname5="george" and so on
I was thinking this might somehow work from a listing.
names="fred,jane,george"
Thy this one. (not tested)
%let names=fred jane george;
%macro test;
%do i=1 %to %sysfunc(countw(&names));
%let var=%sysfunc(scan(&names,&i));
LIBNAME MYXLS2 "K:\place_where_files_go\hb\&var..xls";
PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;
proc sql;
create table myxls2.data as
select fieldname1, fieldname2, fieldname3, fieldname4
from table ???
where upcase(fieldname5)="%upcase(&var)";
quit;
libname MYXLS2 clear;
%end;
%mend;
%test
Linlin,
If that is your untested code, I don't want to be around when you break out the weapons grade fully tested stuff.
That worked first time first run out of the box.
I did this:
data have;
input junkname $;
datalines;
jane
bob
danny
fred
wilbur
george
jorge
;
%let names=fred jane george;
%macro test;
%do i=1 %to %sysfunc(countw(&names));
%let var=%sysfunc(scan(&names,&i));
LIBNAME MYXLS2 "K:\fileplace\&var..xls";
PROC DATASETS LIB=MYXLS2;
DELETE 'data'n;
RUN;
QUIT;
proc sql;
create table myxls2.data as
select junkname
from have
where upcase(junkname)="%upcase(&var)";
quit;
libname MYXLS2 clear;
%end;
%mend;
%test
Created 3 xls files off of the SQL slicker than some new military substance engineered to be super slick.
Thanks.
Can you use PIPE function at SAS?
The following code can get the excel file name you want.
filename x PIPE 'dir K:\place_where_files_go\hb\*.xls /b' ;
And libname statement + dictionary can get very sheets name in every excel file .
Ksharp
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.