The objective was to create an excel work book for each row on Projects List.xls
it is working fine but because some columns contains ALT+ENTER character the files that comes out from such rows will not have the extention xls when i check that with the DOS command DIR/P/W
How can i force the system to save them as EXCEL WITHOUT MANUAL INTERACTION????????????????????????
Any suggestion is welcome
Here is a protion of the code that i created
options noxwait noxsync;
Libname Lock excel "Q:\ADC-DM\DPB\Projects List.xls";
%macro doit;
%do i=1 %to 75;
data _null;
call symput("num",put(&i,z12.));
run;
PROC IMPORT OUT= Project&num.
DATAFILE= "PATH\Projects List.xls"
DBMS=EXCEL REPLACE;
*SHEET="";
RANGE="Current$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
data project&num.;
set Project&num.;
if _n_=&i. then output;
run;
/* Create a unique Excel Spread sheet for each project */
PROC EXPORT DATA= project&num.
OUTFILE= "PATH2\project&num..xls"
DBMS=EXCEL LABEL REPLACE;
sheet="Status";
NEWFILE=YES;
RUN;
Data _null_;
X=Sleep(5,1);
run;
data _null_;
set project&num.;
length name5 $56.;
name_=left(Project_Name___Programmer_Qcer);
name_=trim(name_);
name_=compbl(name_);
name_=translate(name_," "," '%$*#@!?/\.,:()&");
name=compress(left(name_));
name=catx('',name);
name1=translate(name,"_"," ");
name2=compress(name1, "_");
name3=compress(name2);
name4=substr(name3,1,52)||".xls";
name5=translate(name4,"_"," ");
call symput ('prj_nam',name5);
X " cd PATH2 ";
X " move /y project&num..xls &prj_nam. "; /* Rename The file */
run;
%end;
%mend doit;
%doit
... View more