topic Re: File name automation in SAS Programming

File name automation

SASPhile — Fri, 17 Jul 2009 17:26:45 GMT

The excel file names that we recieve every month are not consistent.This forces me to manually change the file name and then process them in SAS.
Is there a way to change the file name programatically?
Say for example , If the file contains "AETNA" , the file name is changed to AETNA_06_2009 for the current month and also the tab is changed the same as file name, hence the SAS dataset name is same as the tab.
Thanks for the help in advance.

Re: File name automation

Peter_C — Sun, 19 Jul 2009 09:59:20 GMT

data steps or macros are the usual choice for deriving things like this. Should be able to find examples in online doc as well as among Samples in http://support.sas.com.

good luck
PeterC

Re: File name automation

sbb — Sun, 19 Jul 2009 15:31:34 GMT

For this objective, the use of SAS functions like INTNX, SYMPUT (using CALL from a DATA step), and possibly the special-purpose %SYSFUNC (macro language call - permits you to integrate DATA step function calls) come to mind with either / both SAS DATA step and MACRO programming techniques.

As mentioned in this thread, the SAS support website can offer both SAS-hosted documentation but more importantly the supplemental technical / conference papers on this type of topic -- explore the site using the SEARCH facility for code automation (topic: using SAS code to generate SAS code) examples.

Scott Barry
SBBWorks, Inc.

Re: File name automation

lu — Mon, 20 Jul 2009 07:06:28 GMT

Hi SASPhile

This simple code will help you

%let my_dir=e:\my_excel_files;
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,'AETNA') 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;

lu

Re: File name automation

DanielSantos — Mon, 20 Jul 2009 10:55:40 GMT

I will add the following to lu's code for the tab renaming issue, (explanation bellow).

Note1: assumes a &FILENAME macro has the renamed filename without extension (say=AETNA_06_2009).
Note2: assumes that the original name of the worksheet to be renamed is "Sheet1".

options noxsync noxwait xmin; * assynchronous execution;

* start Excel DDE server;
x 'excel.exe'; /* you may need to add the full path */

filename xlfile dde 'excel|system';
data _null_;
file xlfile; * start DDE connection and open desired workbook;
put '[OPEN("\&FILENAME..xls")]'; /* replace with your */
put '[WORKBOOK.NEXT()]';
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;
put '=WORKBOOK.NAME("Sheet1","&FILENAME")';
put '=HALT(TRUE)';
put '!dde_flush';
run;

* rename worksheet by running macro;
data _null_;
file xlfile;
put '[RUN("Macro1!r1c1")]';
run;

* delete macro worksheet;
filename xlmacro clear;
data _null_;
file xlfile;
put '[WORKBOOK.ACTIVATE("Macro1")]';
put '[ERROR(FALSE)]';
put '[WORKBOOK.DELETE()]';
put '[ERROR(TRUE)]';
run;

This is a known workaround for a knowned DDE problem (function WORKBOOK.NAME("old_name", "new_name") not working properly when evoked through the DDE engine).
Trick is to create a new worksheet (Macro1) and write there the macro code for the worksheet renaming. Then execute it. Then delete it.

Check Koen Vyverman's SAS paper about DDE:
http://www2.sas.com/proceedings/sugi27/p190-27.pdf

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Re: File name automation

SASPhile — Tue, 21 Jul 2009 20:26:04 GMT

Lu,
does this change the name of the file physically?
cmd="ren &my_dir\"||trim(left(name)) ||' '||compress(left(nm)||put(month(today()),z2.)||'_'||year(today())||'.xls') ;

Re: File name automation

lu — Wed, 22 Jul 2009 05:16:35 GMT

Hi,
This is character string contain command 'rename...' .
to run command on OS - rc=system(cmd);.
And this is change the name of the file physically

lu

Re: File name automation

SASPhile — Wed, 22 Jul 2009 13:43:05 GMT

I tried using this and did not change the name physically!

Re: File name automation

Oleg_L — Wed, 22 Jul 2009 14:10:06 GMT

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;

Re: File name automation

Oleg_L — Wed, 22 Jul 2009 14:42:50 GMT

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;

Re: File name automation

lu — Wed, 22 Jul 2009 17:59:26 GMT

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

Re: File name automation

SASPhile — Wed, 22 Jul 2009 18:12:37 GMT

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

Re: File name automation

lu — Wed, 22 Jul 2009 18:41:31 GMT

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

Re: File name automation

SASPhile — Wed, 22 Jul 2009 19:10:42 GMT

thank you !

Re: File name automation

lu — Thu, 23 Jul 2009 07:50:19 GMT

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

Re: File name automation

lu — Thu, 23 Jul 2009 07:55:55 GMT

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

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

Re: File name automation

SASPhile — Thu, 23 Jul 2009 14:47:35 GMT

Thanks Lu!

Re: File name automation

SASPhile — Thu, 23 Jul 2009 18:26:04 GMT

Question.
How to change the tab name?

Re: File name automation

lu — Thu, 23 Jul 2009 20:47:34 GMT

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

Re: File name automation

SASPhile — Fri, 24 Jul 2009 18:30:05 GMT

Hi Daniel,
I used your code in the bottom half(comments added) and it throws an error:
The top part is working fine.It chages the file name.The tab should be changed to
what ever value is passed to nm_2009_month (in this case nm='Pharmacare';)
Pharmacare_2009_06.
error shown in the end of the 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,'Norditropin Pharmacare') then
do;
nm='Pharmacare';
cmd='ren "'||"&my_dir\"||trim(left(name)) ||'" "'||compress(left(nm)||'_'||year(today())||'_'||put(month(today()),z2.)||'.xls"') ;
put cmd=;
rc=system(cmd);
end;
run;

/*** Daniel's code starts here ***/
* start Excel DDE server;
x 'C:\Documents and Settings\skap\Desktop\inpt'; /* you may need to add the full path */

filename xlfile dde 'excel|system';
data _null_;
file xlfile; * start DDE connection and open desired workbook;
put '[OPEN("&my_dir.\&FILENAME..xls")]'; /* replace with your */
put '[WORKBOOK.NEXT()]';
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;
put '=WORKBOOK.NAME("Sheet1","&FILENAME")';
put '=HALT(TRUE)';
put '!dde_flush';
run;

* rename worksheet by running macro;
data _null_;
file xlfile;
put '[RUN("Macro1!r1c1")]';
run;

* delete macro worksheet;
filename xlmacro clear;
data _null_;
file xlfile;
put '[WORKBOOK.ACTIVATE("Macro1")]';
put '[ERROR(FALSE)]';
put '[WORKBOOK.DELETE()]';
put '[ERROR(TRUE)]';
run;

ERROR: Physical file does not exist, excel|system.