Hi, For the departments, since there are so many, I would suggest you loop over a dataset, instead of hardcoding them in a list. The easiest solution I could think of was do a cartesian join of the 43 depts and 16 months, and loop over that table as shown below. /* sashelp.class is a proxy for your dept list */
data dept (rename=(name=dept));
set sashelp.class(keep=name);
run;
/* Create a list of the months */
data dates;
datstart = '01DEC2016'd ;
datend = '01MAR2018'd ;
format date date10. ;
do i=0 to intck('month',datstart,datend);
date=intnx('month',datstart,i);
output;
end;
drop i datstart datend;
run;
/* Cartesian join to give every combination of dept and month/year */
proc sql noprint ;
create table namedat as select * from dept, dates
order by dept, date;
quit; /* format month<10 with a leading 0 */
proc format; picture mnth other='99';
run;
data want;
set namedat;
year=put(year(date),4.);
month=put(month(date),mnth.);
run;
/* The following code for looping over a dataset is based on the paper by Moore, E. */
/* http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/a000093638.htm */
/* Performing Multiple Statements for Each Record in a SAS® Data Set by Moore, Edward */
%macro datapull;
DATA _NULL_;
IF 0 THEN SET want NOBS=n;
CALL SYMPUT('COUNT',n);
STOP;
RUN;
/* loop over each record */
%DO i=1 %TO &COUNT;
DATA _NULL_;
SET want (FIRSTOBS=&i);
CALL SYMPUT('deparmentname',trim(dept));
CALL SYMPUT('yr',year);
CALL SYMPUT('mo',left(put(month,$2.)));
STOP;
RUN;
%let OUT=&deparmentname._&yr._&mo;
%let path=/data;
%let DAT=&path./&yr./&mo./&deparmentname..xlsx;
%put &out;
%put &dat;
libname XL XLSX "&dat";
data &out;
set xl.sheet1; /* assuming your worksheet is named Sheet1 */
run;
LIBNAME XL CLEAR;
%END;
%mend datapull;
%datapull;
... View more
