topic Re: proc export create files with missing extension " xls" in SAS Procedures

proc export create files with missing extension " xls"

WorldCup10 — Wed, 25 Nov 2009 15:47:38 GMT

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

Re: proc export create files with missing extension " xls"

sbb — Wed, 25 Nov 2009 17:49:33 GMT

To start, can you identify what the file-extension will be with the command below, when executed:

X " move /y project&num..xls &prj_nam. "; /* Rename The file */

Also, you may want to get that sticky QUESTION_MARK key looked at.

Scott Barry
SBBWorks, Inc.

Re: proc export create files with missing extension " xls"

WorldCup10 — Wed, 25 Nov 2009 18:13:59 GMT

"MOVE" is a DOS command It renames files
When it is used with /Y the system will not prompt you to confirm the overwrite if the file initially exist.

BTW your question has led to me to change my line of code to

X " move /y project&num..xls &prj_nam..xls "; instead of

X " move /y project&num..xls &prj_nam. ";

but still the problem persists

Re: proc export create files with missing extension " xls"

sbb — Wed, 25 Nov 2009 19:07:09 GMT

Okay, now please explain the exact problem symptom you are experiencing and also what you are expecting the outcome to be. Also, provide information about your SAS environment, such as OS, version, remote or local SAS execution. And if you have any warnings/errors being generated, listing those diagnostics would be most useful.

Scott Barry
SBBWorks, Inc.

SAS/Access Interface to PC Files: Reference, Microsoft Excel Workbook Files
Overview of Microsoft Excel Files Essentials
http://support.sas.com/documentation/cdl/en/acpcref/61891/HTML/default/a003103761.htm

Re: proc export create files with missing extension " xls"

WorldCup10 — Wed, 25 Nov 2009 20:27:33 GMT

> Okay, now please explain the exact problem symptom
> you are experiencing and also what you are expecting
> the outcome to be. Also, provide information about
> your SAS environment, such as OS, version, remote or
> local SAS execution. And if you have any
> warnings/errors being generated, listing those
> diagnostics would be most useful.
>
> Scott Barry
> SBBWorks, Inc.
>
> SAS/Access Interface to PC Files: Reference,
> Microsoft Excel Workbook Files
> Overview of Microsoft Excel Files Essentials
> http://support.sas.com/documentation/cdl/en/acpcref/61
> 891/HTML/default/a003103761.htm

It is a PC SAS 9.2 on XP_PRO platform
The log is clean there is no Warning or error messages
Now I suspect the problem to be due to DOS and not SAS because during the execution Excel gets created and once it reaches the phase of renaming files things gets screwed i have refreshed the contents of "Path2" the location where files gets dumped continously and i noticed that when the loop reaches for example at 6 where xls extention did not occur i see that project000000000006.xls gets initially created and then later renaming it based on the contents of certain excel cell leads to a file with no extention DMFY2010CustomerRequirements

Re: proc export create files with missing extension " xls"

sbb — Wed, 25 Nov 2009 20:39:38 GMT

I also see a CALL SYMPUT towards the end of your DATA step directly followed by a reference to the related macro variable, which will not work, without adding a RUN; statement before the X commands.

Adding the following would help you debug the program execution, step by step, and most importantly the DATA step execution:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC;

Scott Barry
SBBWorks, Inc.

Re: proc export create files with missing extension " xls"

sbb — Wed, 25 Nov 2009 20:40:46 GMT

Very useful SAS DOC on this topic:

SAS Macro Language: Reference, Scopes of Macro Variables
Special Cases of Scope with the CALL SYMPUT Routine
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/tw3514-symput.htm

Scott Barry
SBBWorks, Inc.