File name automation

[lu]

SASPhile,
pls let me to know:
1.do you use Windows or Unix/Linux OS?
2.What do you see in LOG after statement put cmd=; ?
3.chek in data set d variable RC = ???

lu

[SASPhile]

I use windows.
This is the code I used.

The input file present in my_dir is:
2009 June GH Utilization - Unblinded.xls
My objective is 2009 June GH Utilization is present
then chnage the file name to caremark_2009_07.xls
and the tab to caremark_2009_07.

%let my_dir=C:\Documents and Settings\skap\Desktop\inpt;
filename dir pipe "dir &my_dir";

data d;
infile dir pad truncover;
input date $10.@;
if date ='' or date='Volume' or length(date) <10 then delete;
else input time $ ampm $ dir $ name $20.;
if trim(dir)='' then delete;
if index(name,'GH Utilization') then
do;
nm=scan(name ,1,'.');
cmd="ren &my_dir\"||trim(left(name)) ||' '||compress(left(nm)||put(month(today()),z2.)||'_'||year(today())||'.xls') ;
put cmd=;
rc=system(cmd);
end;
run;

The log is:

623 %let my_dir=C:\Documents and Settings\skap\Desktop\inpt;
624 filename dir pipe "dir &my_dir";
625
626 data d;
627 infile dir pad truncover;
628 input date $10.@;
629 if date ='' or date='Volume' or length(date) <10 then delete;
630 else input time $ ampm $ dir $ name $20.;
631 if trim(dir)='' then delete;
632 if index(name,'GH Utilization') then
633 do;
634 nm=scan(name ,1,'.');
635 cmd="ren &my_dir\"||trim(left(name)) ||'
635! '||compress(left(nm)||put(month(today()),z2.)||'_'||year(today())||'.xls') ;
636 put cmd=;
637 rc=system(cmd);
638 end;
639 run;

NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
635:94
NOTE: The infile DIR is:
Unnamed Pipe Access Device,
PROCESS=dir C:\Documents and Settings\skap\Desktop\inpt,
RECFM=V,LRECL=256

Stderr output:
The system cannot find the path specified.
NOTE: 0 records were read from the infile DIR.
NOTE: The data set WORK.D has 0 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds

[lu]

ok ,
the problem is blanks in path - C:\Documents and Settings\skap\Desktop\inpt;
try something without blank - c:\inpt; . If you must this path I will fix it tomorrow
I haven't access to SAS Windows now .

lu

[SASPhile]

thank you !

[lu]

Hi ,SASPhile, Fixed code:
options noxwait;
%let my_dir=C:\Documents and Settings\skap\Desktop\inpt;
data _null_;
dir_cmd='dir "'||"&my_dir"||'"';
rc=filename('dir',dir_cmd,'pipe');
run;
data d;
infile dir pad truncover;
input date $10.@;
if date ='' or date='Volume' or length(date) <10 then delete;
else input time $ ampm $ dir $ name $200.;
if trim(dir)='' then delete;
if index(name,'GH Utilization') then
do;
nm='caremark';
cmd='ren "'||"&my_dir\"||trim(left(name)) ||'" "'||compress(left(nm)||'_'||year(today())||'_'||put(month(today()),z2.)||'.xls"') ;
put cmd=;
rc=system(cmd);
end;
run;
lu. Message was edited by: lu

[lu]

put cmd=;
rc=system(cmd);
end;
run;
lu.

P.S bug in forum ??? cut my previous message Message was edited by: lu

[SASPhile]

Thanks Lu!

[SASPhile]

Question.
How to change the tab name?

[lu]

What about Daniel Santos's answer ?
http://support.sas.com/forums/message.jspa?messageID=23303#23303
lu

[DanielSantos]

Hello.

OK. I couldn't get your renaming piece of code to work properly at my workstation (maybe something missing that was crunched from the post?), so in order to test the complete solution, I ended up redesigning the file renaming piece (same thing, another approach) and adjusting the tab renaming macro.

I have also introduced some artificial delays (1 second each, and adjustable), because I suspect that there is maybe a lag introduced by excel's launch or/and the issued rename command.

This code was tested and is working on my SAS 9.1.3 SP4/MS Excel 2003 (WinXP 32b) workstation configuration.

Just need to adjust the macro variables MY_DIR, SOURCE and TARGET to suit your needs (see code bellow).

PLEASE verify that the path of MS Excel is correct and that your regional settings specify the ; char as the list separator. If not, please do the necessary modifications (see comments) on the code bellow:

options noxsync noxwait xmin; * assynchronous execution;

/************************************************************************/

* macro for excel worksheet renaming;
%macro ren_sheettab(DIRNAME,FILENAME);

* DIRNAME = directory name of the excel file (no tailing \);
* FILENAME = filename of the excel (no .extension);

* alocates connection do DDE server;
filename xlfile dde 'excel|system'; * assumes DDE server is running;

data _null_;
file xlfile; * inits DDE connection;
put "[OPEN(""&DIRNAME\&FILENAME..xls"")]"; * opens desired workbook;
put '[WORKBOOK.NEXT()]'; * activates the next worksheet;
put '[WORKBOOK.INSERT(3)]'; * insert new macro worksheet;
run;

* prepare macro for worksheet renaming;
filename xlmacro dde 'excel|Macro1!r1c1:r2c1' notab;
data _null_;
file xlmacro;
* write macro code;
/***** adjust the char list separator if necessary!!! *****/
put "=WORKBOOK.NAME(""Sheet1"";""&FILENAME"")"; * uses name function;
/***** adjust the char list separator if necessary!!! *****/
put '=HALT(TRUE)'; * halts macro;
put '!dde_flush'; * flushes DDE writte buffer;
run;
* run macro for tab renaming;
data _null_;
file xlfile; * inits DDE connection;
put '[RUN("Macro1!r1c1:r2c1")]'; * run macro;
put '[ERROR(FALSE)]';
run;

* delete macro worksheet, save and quit;
filename xlmacro clear;
data _null_;
file xlfile;
put '[WORKBOOK.ACTIVATE("Macro1")]'; * activate macro worksheet;
put '[ERROR(FALSE)]';
put '[WORKBOOK.DELETE()]'; * delete macro worksheet;
put '[ERROR(TRUE)]';
put '[SAVE()]'; * save workbook with renamed worksheet;
put '[FILE.CLOSE(FALSE)]'; * close file;
put '[QUIT()]'; * close DDE server;
run;

%mend ren_sheettab;

/************************************************************************/

* 1. renames FIRST file matching *&SOURCE*.xls to &TARGET._YYYY_MM.xls;
* 2. renames 'Sheet1' of the renamed file to to &TARGET._YYYY_MM;

%let MY_DIR=D:\temp; * directory to scan;
%let SOURCE=TESTE; * source text to search within filename;
%let TARGET=XPTOX; * target text (prefix) to use for filename renaming;

* start Excel DDE server;
data _null_;
/***** adjust to your path if necessary!!! *****/
x '"C:\Program Files\Microsoft Office\OFFICE11\excel.exe"';
/***** adjust to your path if necessary!!! *****/
call sleep(1,1); * custom delay of 1 second;
run;

* scan directory, search for mathcing filename, rename file, rename tab.;
data _null_;
* associate directory;
if not filename('MY_DIR',"&MY_DIR") then do;
D_ID=dopen('MY_DIR'); * open directory;
if not D_ID then stop;
M_IDX=1; * init directory member index;
do until (M_IDX gt dnum(D_ID)); * until there are no members;
NAME=dread(D_ID,M_IDX); * get member name;
F_ID=mopen(D_ID,NAME); * try to open member;
RC=fclose(F_ID); * close member;
* if it is a file and extension is .xls and source matches the name;
if F_ID and
upcase(substrn(strip(reverse(NAME)),1,4)) eq 'SLX.' and
index(upcase(NAME),upcase("&SOURCE")) then do;
* rename file;
TARGET=catx('_',"&TARGET",put(year(today()),z4.),put(month(today()),z2.));
call system("ren &MY_DIR\"!!strip(NAME)!!' '!!strip(TARGET)!!'.xls');
* rename tab;
call sleep(1,1); * custom delay of 1 second;
call execute('%ren_sheettab('!!strip("&MY_DIR")!!','!!strip(TARGET)!!');');
RC=dclose(D_ID); * close directory;
stop; * stop dataset execution;
end;
M_IDX+1; * update index to next directory member;
end;
RC=dclose(D_ID); * close directory;
end;
run;

It may look very complex, but actually it is not. Almost every line is commented to help understanding.

I'll post here some documentation after you get this to work.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

[SASPhile]

Santos,
The inputs from myside are provided as follows:
%let MY_DIR=C:\Documents and Settings\skap\Desktop\inpt; * directory to scan;
%let SOURCE=2009 June GH Utilization - Unblinded; * source text to search within filename;
%let TARGET=IVsolutions; * target text (prefix) to use for filename renaming;


my_dir is directory where excel files are saved.
source is the excel file that needs the name to changed
target is the excel file to change to : i.e Ivsolutions_yyyy_mm.xls

in the code dirname and filename are being resolved.Am I doing anything wrong?

Log:
%let MY_DIR=C:\Documents and Settings\skap\Desktop\inpt; * directory to scan;
%let SOURCE=2009 June GH Utilization - Unblinded; * source text to search within filename;
%let TARGET=IVsolutions; * target text (prefix) to use for filename renaming;

ERROR: DDE syntax error in physical name.

[DanielSantos]

OK I figured out that your using windows long filenames and I'm not.

Please locate and replace the following line:
call system("ren &MY_DIR\"!!strip(NAME)!!' '!!strip(TARGET)!!'.xls');

With this one:
call system("ren ""&MY_DIR\"!!strip(NAME)!!'" "'!!strip(TARGET)!!'.xls"');

Now it should support long filenames (at least here it now works fine).

Next...

Is your MS Excel executable located here?
C:\Program Files\Microsoft Office\OFFICE11\excel.exe

And...

Check if your regionals settings are the same as mine by opening MS Excel and typing the following formula in any cell. Does it produces a error?

=CONCATENATE("First";"Middle";"Last")

Little by little, we will get there.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

[SASPhile]

Santos,
Is your MS Excel executable located here?
C:\Program Files\Microsoft Office\OFFICE11\excel.exe----YES!

=CONCATENATE("First";"Middle";"Last")----NO!

[SASPhile]

Santos,
I tried to execute the code and it took long time to run.I waited for 5 minutes and then had to cancel the submission.Am I doing anythking wrong?

[DanielSantos]

OK. We're getting somewhere.

SAS seems not to be communicating with the DDE server (excel).

Simultaneously you should have an Excel window opened. Check there what's happening.
Does it have some message?
Did it opened the right file?
Is it opened on a blank worksheet?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.