hello. So here is a tweak of Tom's and some other codes to export excel files (i) based on char values from a user 'name' column (eg., where values like John, Jane, etc.) , and (ii) table name(s) of all tables within given library (eg., work.table_a; ...table_b) such as the excel file name is the user 'name' value, and each sheet name is equal to the table names (where user name value is found)...there may be a more efficient means of coding this. %Let Libname=IM_RE; /* edit libname here */
%put &libname;
%MACRO Export;
proc sql noprint;
select count(distinct Business_Owner) /* Edit source column as needed */
into :obs
from &libname..quad1; /* Edit source table as needed*/
quit;
proc sql noprint;
select distinct Business_Owner
into :name1- notrim
from &libname..quad1 (where=(Business_Owner ne ''));
quit;
/*edit: adding a 'distinct' in lieu of'group' segment*/
proc sql; create table test as
select distinct LIBNAME, memname
from dictionary.columns
WHERE LIBNAME=upcase("&Libname")
;quit;
DATA MEMBERS;
SET test;
RETAIN OBS 0;
OBS = OBS+1;
KEEP MEMNAME OBS;
RUN;
PROC SQL noprint;
SELECT MIN(OBS), MAX(OBS) INTO :MIN, :MAX trimmed FROM MEMBERS;
QUIT;
%Local D;
%DO D = &MIN %TO &MAX;
/*b*/
%do i = 1 %to &obs;
/*b*/
PROC SQL noprint;
SELECT COMPRESS(MEMNAME) INTO: Table trimmed
FROM MEMBERS
WHERE OBS=&D;
QUIT;
%let Table=%trim(&Table);
PROC EXPORT /*exports to a default unix server. Add a filepath to outfile as needed*/
DATA=&libname..&Table (where=(Business_Owner= "&&name&i"))
OUTFILE="%qsubstr(&&name&i.,1).xlsx"
DBMS=xlsx replace;
SHEET="&Table";
run;
/* 19.03.18.9:00am –optional code to generate tables which is good
for data validation
data &libname..&Table._&&name&i;
set &libname..&Table;
where Business_Owner = "&&name&i";
run; */
%end;
%end;
%MEND Export;
Options symbolgen mlogic ;
/*Remove above line when macro working .For efficiency, consider putting
Options NoSymbolgen nomlogic ;
*/
%Export;
... View more