Hi all,
I have a SAS program which reads in Excel spreadsheets and moves the files listed within from one location to another.
As part of this program, I am trying to build a ReadMe file which:
1 Is a text file
2 Lives in the original directory of the file
3 Contains:
a) List of files moved
b) Starting loc
c) Ending loc
d) Date
I have a sequence which is working, BUT in doing runs, it is getting some error messages if either the directory or file name has weird characters (I've seen comma and hyphen as examples - there could probably be others):
Here is what I have. It is using values in the data for directory, new directory, and file name to build everything. What I'd like is advice on how to modify the sequence so it is robust to these weird characters:
/*OUTPUT A README TO EACH ORIGINAL DIRECTORY WITH FILE NAME, DATE MOVED, ORIG LOCATION, AND NEW LOCATION*/
%macro readme;
/*BUILD LIST OF DISTINCT DIRECTORIES AND HOW MANY FILES IN EACH*/
proc sql noprint;
select distinct directory
into :dist_dir separated by '^'
from xl_file_move
;
quit;
proc sql noprint;
select distinct new_loc
into :dist_new_loc separated by '^'
from xl_file_move
;
quit;
proc sql noprint;
select count(directory)
into :num_files separated by '^'
from xl_file_move
group by directory;
quit;
/*BUILD MACRO VAR WHICH COUNTS DISTINCT DIRECTORIES*/
proc sql noprint;
select count(distinct directory)
into :num_dist_dir
from xl_file_move
;
quit;
%put num_dist_dir = &num_dist_dir.;
%do i = 1 %to &num_dist_dir.;
%let this_dir = %scan(&dist_dir., &i., ^);
%let this_dir2 = %scan(&dist_new_loc., &i., ^);
%put this_dir = &this_dir.;
%put this_dir2 = &this_dir2.;
/*BUILD MACRO LIST OF FILES IN THIS DIR*/
proc sql noprint;
select fname
into :list_of_files separated by '^'
from xl_file_move
where directory = "&this_dir." ;
quit;
/*BUILD FORMATTED DATE VAR*/
%let dt_fmt = %sysfunc(today(), mmddyy10.);
proc format;
value $rm
'0' = "The following files have been moved from &this_dir. to"
'0a'= "&this_dir2. on &dt_fmt."
;
run;
data readme (drop=v1);
length v1 $5. v2 $100.;
v1='0';
v2='0';
output;
v2='0a';
output;
/*OUTPUT ONE REC FOR EACH FILE*/
%let num_files_for_dir = %scan(&num_files., &i., ^ );
%do j = 1 %to &num_files_for_dir.;
%let file = %scan(&list_of_files.), &j., ^);
v1 = "&j.";
v2 = "&file.";
output;
%end;
format v2 $rm.;
run;
proc export
data=readme
outfile="&this_dir.\Readme_&sysdate..txt"
dbms=tab
replace;
putnames=no;
run;
%end;
%mend readme;
%readme;
... View more
