I have an xls spreadsheet that I am going to use as a template. It will consist of 1 tab and I'll call the tab Sheet1. What I would like to do is create a tab for each provider number that exists in a data set (called priority_scale below). I don't know what the activesheet syntax should be (other than it shouldn't be what I've written) and I'm sure I need a little help with the code after the first copy occurs. Any help is much appreciated.
Thanks!
OPTIONS NOXWAIT NOXSYNC;
X "'C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE'";
DATA _NULL_;
zzzz = SLEEP(2);
RUN;
FILENAME CMDEXCEL DDE 'EXCEL|SYSTEM';
DATA _NULL_;
FILE CMDEXCEL;
PUT "[OPEN(""N:\PUBLIC\Chip's Adhocs\MISC\priority_scale.xls"")]";
RUN;
%MACRO BYPRV(PROV);
data _null_;
file CMDEXCEL;
ACTIVESHEET.COPY AFTER:=ACTIVESHEET.NAME="&PROV";
RUN;
%MEND BYPRV;
data _null_;
set PRIORITY_SCALE;
BY PROV;
IF FIRST.PROV THEN DO;
call execute('%BYPRV('||PROV||')');
END;
run;
quit;