File name automation

[SASPhile]

I havent tried yet.I will let know once I try it!

[SASPhile]

Santos,
I couldnt get to work on the task we discussed so far.I tried the solution that you provided,but it still looks like the worksheet name is not being changed.
I'm succesful to the part of changing the file name programactially,
SASPhile.

[DanielSantos]

When running the code you should have an Excel window opened.
Please 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.

[SASPhile]

This is what I used to change the file name only!
Did not know how to proceed to change the worksheet name.

options noxwait;
%let my_dir=T:\NovoLand\sspr\Redistribution Data (SP and WK data)\Raw Data\MonthlyInputfiles;
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='IVSOLUTIONS';
cmd='ren "'||"&my_dir\"||trim(left(name)) ||'" "'||compress(left(nm)||'_'||year(today())||'_'||put(month(today())-1,z2.)||'.xls"') ;
put cmd=;
rc=system(cmd);
end;
run;

[Oleg_L]

Just to support you.
I did not find the solution of changing the worksheet name in MS Excel of versions 97 - 2003.
But for output reports that need changing of worksheet name i use Excel 95.
All macrofunctions of Excel work nice through DDE in Excel 95.

[DanielSantos]

Well it's working with me, on MS Excel 2003.

But I guess, it could be some kind of system configuration.

Still... and so I can help you.

When running the code you should have an Excel window opened.
Please 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.

[Oleg_L]

Daniel,
can you put a simple example of code that works on Excel 2003.
The code that renames "sheet1" to "new" in already opened "Book1".
I know the only way is VBA macro running from SAS.
But i don't know VBA, so I have difficulties with Excel 2003.

[SASPhile]

Same here.

[SASPhile]

Hi Lu,
How to make the index search which is not case sensitive?
if index(name,'GH Utilization') then do;
sas code
end;
here 'GH Utilization' can be in any case..upper, lower ,mixed or Prop case.

[data_null__]

[pre]find('GH Utilization',name,'IT')[/pre]

[SASPhile]

Did you mean to use in place of index?

[SASPhile]

Did you mean to use in place of index?

[sbb]

I would say the answer is YES - FIND provides functionality that INDEX does not, specifically case-blind, given the third argument. Have a look at the DOC:

http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a002267763.htm

Scott Barry
SBBWorks, Inc.

Suggestion: This is the exact reason to break up these piggy-backed threads! It's clearly a different subjec/topic/problem.

[Oleg_L]

Hi.

Try this macro (if you work under Windows)

%macro rename(input=d:\work\test rename,m=06,y=2009);
%let dlm=\;
%let filrf=mydir;
%let rc=%sysfunc(filename(filrf,"&input"));
%let did=%sysfunc(dopen(&filrf));
%let lstname=;
%let memcount=%sysfunc(dnum(&did));
%if &memcount > 0 %then %do;
%do i=1 %to &memcount;
%let lstname=%sysfunc(dread(&did,&i));
%let file=&input&dlm&lstname;
%if %sysfunc(index(&lstname,AETNA)) ne 0
%then %do;
x move "&file" "&input&dlm.AETNA_&m._&y";
%end;
%end;
%let rc=%sysfunc(dclose(&did));
%end;
%mend rename;

options mprint symbolgen noxwait;
%rename;

[Oleg_L]

Sorry.
I forgot upcase checking and xls extension for out:

%macro rename(input=d:\work\test rename,m=06,y=2009);
%let dlm=\;
%let filrf=mydir;
%let rc=%sysfunc(filename(filrf,"&input"));
%let did=%sysfunc(dopen(&filrf));
%let lstname=;
%let memcount=%sysfunc(dnum(&did));
%if &memcount > 0 %then %do;
%do i=1 %to &memcount;
%let lstname=%sysfunc(dread(&did,&i));
%let file=&input&dlm&lstname;
%if %sysfunc(index(%sysfunc(upcase(&lstname)),AETNA)) ne 0
%then %do;
x move "&file" "&input&dlm.AETNA_&m._&y..xls";
%end;
%end;
%let rc=%sysfunc(dclose(&did));
%end;
%mend rename;

options mprint symbolgen noxwait;
%rename;