DATA Step, Macro, Functions and more

File name automation

Reply
Super Contributor
Posts: 647

File name automation

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.
Valued Guide
Posts: 2,175

Re: File name automation

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
Super Contributor
Super Contributor
Posts: 3,174

Re: File name automation

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.
Contributor lu
Contributor
Posts: 23

Re: File name automation

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
Super Contributor
Posts: 474

Re: File name automation

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.
Super Contributor
Posts: 647

Re: File name automation

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'Smiley Wink
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.
Super Contributor
Posts: 474

Re: File name automation

Hello SASPhile.

You got it wrong.

ALL the code should be use, not only part of it.

Since you're renaming the files by cycling through a datastep, and the sample of code I have posted is intend to deal with only one file, You should encapsulate the code into a macro and call it from the datastep, say with a call execute.

Something like this:

/* THIS GOES AT THE TOP OF YOUR SCRIPT */

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';

/* THIS GOES AT THE TOP OF YOUR SCRIPT */

%macro ren_sheettab(DIRNAME,FILENAME);

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

data _null_;
file xlfile; * start DDE connection and open desired workbook;
put '[OPEN("&DIRNAME\&FILENAME..xls")]';
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;

%mend ren_sheettab;


Now, from within you datastep renaming routine you could call execute the above macro, say, like this:

...

call execute ("%ren_sheettab(&my_dir,"||
catx('_',nm,put(year(today()),Z4.),put(month(today()),Z2.))||');');

...

Now, if you are having trouble understanding this piece of code, please say so.
There is no real purpose on helping someone if no knowledge is transmitted.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Message was edited by: Daniel Santos
Super Contributor
Posts: 647

Re: File name automation

Hi Santos,
I tried the same as told by you.But I wasnt successful.
Below is the combined code:
/**********************************************************/
%macro ren_sheettab(DIRNAME,FILENAME);

data _null_;
file xlfile; * start DDE connection and open desired workbook;
put '[OPEN("&DIRNAME\&FILENAME..xls")]';
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;

%mend ren_sheettab;



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='IvSolutions';
cmd='ren "'||"&my_dir\"||trim(left(name)) ||'" "'||compress(left(nm)||'_'||year(today())||'_'||put(month(today()),z2.)||'.xls"') ;
put cmd=;
rc=system(cmd);

end;
call execute ("%ren_sheettab(&my_dir,"||catx('_',nm,put(year(today()),Z4.),put(month(today()),Z2.))||');');

run;
/***********************************/
I guess &my_dir takes the positional paramater for dirname.and for filename?Pardon my ignorance.

below is the log:
1 data _null_; file xlfile; * start DDE connection and open desired workbook; put
1 ! '[OPEN("&DIRNAME\&FILENAME..xls")]'; put '[WORKBOOK.NEXT()]'; put '[WORKBOOK.INSERT(3)]'; *
----
49
1 ! insert new macro worksheet; run; * prepare macro for worksheet renaming;
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

NOTE: Line generated by the macro variable "DIRNAME".
1 "data _null_; file xlfile; * start DDE connection and open desired workbook; put
--------------------------------------------------------------------------------- -
-
49
388
388

76
-

200
1 ! '[OPEN("C:\Documents and Settings\skap\Desktop\inpt
NOTE: Line generated by the invoked macro "REN_SHEETTAB".
2 filename xlmacro dde 'excel|Macro1!r1c1:r2c1' notab; data _null_; file xlmacro; * write
2 ! macro code; put '=WORKBOOK.NAME("Sheet1","&FILENAME")'; put '=HALT(TRUE)'; put '!dde_flush'
----------------------------------------
49
2 ! ; run; * rename worksheet by running macro; data _null_; file xlfile; put
NOTE: Line generated by the invoked macro "REN_SHEETTAB".
3 '[RUN("Macro1!r1c1")]'; run; * delete macro worksheet; filename xlmacro clear; data
-------------------------------------------------------------------
49
3 ! _null_; file xlfile; put '[WORKBOOK.ACTIVATE("Macro1")]'; put '[ERROR(FALSE)]'; put
3 ! '[WORKBOOK.DELETE()]'; put '[ERROR(TRUE)]'; run;
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.
Super Contributor
Posts: 474

Re: File name automation

Hello.

From some reason your code seem to be cut at the end...

Could you please post the rest of it?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
Super Contributor
Posts: 647

Re: File name automation

end;
call execute ("%ren_sheettab(&my_dir,"||catx('_',nm,put(year(today()),Z4.),put(month(today()),Z2.))||');');

run;
/**********************/
The positional paramater for dirname is my_dir.

Log:
NOTE: Line generated by the invoked macro "REN_SHEETTAB".
1 data _null_; file xlfile; * start DDE connection and open desired workbook; put
1 ! '[OPEN("&DIRNAME\&FILENAME..xls")]'; put '[WORKBOOK.NEXT()]'; put '[WORKBOOK.INSERT(3)]';
---
49
1 ! * insert new macro worksheet; run; * prepare macro for worksheet renaming;
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

NOTE: Line generated by the macro variable "DIRNAME".
1 "data _null_; file xlfile; * start DDE connection and open desired workbook; put
--------------------------------------------------------------------------------- -
-
49
388
388

76

-

200
1 ! '[OPEN("C:\Documents and Settings\skap\Desktop\inpt
NOTE: Line generated by the invoked macro "REN_SHEETTAB".
2 filename xlmacro dde 'excel|Macro1!r1c1:r2c1' notab; data _null_; file xlmacro; * write
2 ! macro code; put '=WORKBOOK.NAME("Sheet1","&FILENAME")'; put '=HALT(TRUE)'; put
----------------------------
49
2 ! '!dde_flush'; run; * rename worksheet by running macro; data _null_; file xlfile; put
NOTE: Line generated by the invoked macro "REN_SHEETTAB".
3 '[RUN("Macro1!r1c1")]'; run; * delete macro worksheet; filename xlmacro clear; data
-------------------------------------------------------------------
49
3 ! _null_; file xlfile; put '[WORKBOOK.ACTIVATE("Macro1")]'; put '[ERROR(FALSE)]'; put
3 ! '[WORKBOOK.DELETE()]'; put '[ERROR(TRUE)]'; run;
NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS
release. Inserting white space between a quoted string and the succeeding
identifier is recommended.

ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.

1051
1052 run;
NOTE: DATA statement used (Total process time):
real time 5.17 seconds
cpu time 0.06 seconds
Super Contributor
Posts: 474

Re: File name automation

Sorry, my bad.

Replace the call execute by this one, and place it within the do; end; just after issuing the rename command.

call execute ('%ren_sheettab('||"&my_dir,"||catx('_',nm,put(year(today()),Z4.),put(month(today()),Z2.))||');');

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
Super Contributor
Posts: 647

Re: File name automation

Santos,
Sorry for the touble.I'm sending you the errors generated.

Question:Whats the parameter passed to the macro variable filename?


NOTE: CALL EXECUTE generated line.
1 + data _null_; file xlfile; * start DDE connection and open desired workbook; put
'[OPEN("&DIRNAME\&FILENAME..xls")]'; put '[WORKBOOK.NEXT()]'; put '[WORKBOOK.INSERT(3)]'; *
insert new macro worksheet; run;

ERROR: DDE syntax error in physical name.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds



1 +
* prepare macro for worksheet renaming; filename
2 + xlmacro dde 'excel|Macro1!r1c1:r2c1' notab;
2 + data _null_; file xlmacro; * write macro
code; put '=WORKBOOK.NAME("Sheet1","&FILENAME")'; put '=HALT(TRUE)'; put '!dde_flush'; run;

ERROR: Physical file does not exist, excel|Macro1!r1c1:r2c1.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds



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

ERROR: DDE syntax error in physical name.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds



3 + * delete macro worksheet; filename xlmacro clear;
NOTE: Fileref XLMACRO has been deassigned.
3 + data _null_;
file xlfile; put '[WORKBOOK.ACTIVATE("Macro1")]'; put '[ERROR(FALSE)]'; put
'[WORKBOOK.DELETE()]'; put '[ERROR(TRUE)]'; run;

ERROR: DDE syntax error in physical name.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds



3 +
;
Super Contributor
Posts: 647

Re: File name automation

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') ;
Contributor lu
Contributor
Posts: 23

Re: File name automation

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
Super Contributor
Posts: 647

Re: File name automation

I tried using this and did not change the name physically!
Ask a Question
Discussion stats
  • 44 replies
  • 425 views
  • 0 likes
  • 7 in conversation