Weird characters messing up directory/file name macros

[Walternate]

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;

[Quentin]

If your files have commas in them, it's likely that you will need to add some macro quoting functions.   You could try replacing all of your calls to %scan with %qscan, and that might do it.

Can you run your code with options MPRINT turned on, and post the log from processing a file with problematic characters in the name that shows the name of the file, and also shows the error messages?

[Tom]

Please share example input and expect output.

A quick scan of the code makes it look like you are starting from a dataset named xl_file_move and creating a dataset named readme which is then written to a tab delimited text file.

I do not understand why you are moving ANY of this data into macro variables.   You could most likely do this all with simple data steps and then commas or hyphens will not cause any trouble at all.

[Tom]

Looks like you just want to run something like this data step.

data _null_;
  set xl_file_move ;
  by directory new_loc ;
  length readme $256 ;
  date_string = "%sysfunc(date(),yymmdd10.)";
  readme = cats(directory, '/readme_',date_string,'.txt');
  file readme filevar=readme ;
  if first.new_loc then do ;
    row=0;
    put 'The following files have been moved from ' directory 'to ' new_loc 'on ' date_string ;
  end;
  row+1;
  put row fname ;
run;

Although it would be better if the date_string was actually one of the variables in the source list of files.  In that case you would want to add DATE_STRING to the BY statement between DIRECTORY and NEW_LOC so that all of the files from DIRECTORY on the same date are documented in the same text file.