BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
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.
44 REPLIES 44
Peter_C
Rhodochrosite | Level 12
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
lu
Calcite | Level 5 lu
Calcite | Level 5
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
DanielSantos
Barite | Level 11
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.
SASPhile
Quartz | Level 8
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.
DanielSantos
Barite | Level 11
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
SASPhile
Quartz | Level 8
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.
DanielSantos
Barite | Level 11
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.
SASPhile
Quartz | Level 8
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
DanielSantos
Barite | Level 11
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.
SASPhile
Quartz | Level 8
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 +
;
SASPhile
Quartz | Level 8
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') ;
lu
Calcite | Level 5 lu
Calcite | Level 5
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
SASPhile
Quartz | Level 8
I tried using this and did not change the name physically!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 44 replies
  • 1806 views
  • 0 likes
  • 7 in conversation