Valid Var Name not working and Importing Excel Files

[BenCBanks]

I am attempting to use this code (see below) to import multiple excel files (more than 100) that should all have the same file format. However, they are all littered with awful naming conventions, hence the options validvarname=any; option at the top. However, this does not work and I keep on getting errors like about the file name being an invalid SAS name. Like "ERROR: The value 'REPORT_2018-10-20_01-00-04_4'n is not a valid SAS member name"

Also, on a side note. I do plan on appending all of these files together, whenever I can get this to run appropriately. Would take suggestions on how to make the code better also. 

EDIT: I was able to get the member name right, I was confused as to the different between MEMBERNAME and VARNAME. Now, I could still use some pointers on appending the data correctly. 

Does anyone know why this is the case? This code is an adaptation of the code I found here: https://communities.sas.com/t5/General-SAS-Programming/Importing-several-files-into-SAS/td-p/241827

filename fref "/pmsdat/Strat/dump" ;

options validvarname=any; /* to allow non-standard dataset names */
 
/* Making a list of all files in the folder */
data FilesInFolder;
   length Line 8 File $300;
   List = dopen('fref');  /* corrected the function argument */
   do Line = 1 to dnum(List);
        File = compress(trim(dread(List,Line)));
        output;
   end;
   drop list line;
run;
 
/* Creating global macro variables */  /* not "local" */
data _NULL_;
     set FilesInFolder end=final;
     call symput(cats('File', _N_), trim(File));     /* used CATS instead of COMPRESS (...||...) */
     call symput(cats('Name', _N_), trim(nliteral(substr(File,1,min(28, length(File)))))); /* inserted */
     if final then call symputx(trim('Total'), _N_); /* replaced symput by symputx */
run;


/* This macro should import all files specified in the list and save them as datasets */
%macro loop;

%do i = 1 %to &Total;

  proc import datafile="/pmsdat/Strat/dump/&&File&i"
       out=WORK.&&name&i  /* adapted */
       dbms=xlsx
	   
       replace;
		sheet="visits";
		getnames=yes;
  run;
%end;
%mend loop;
 
%loop

[ballardw]

---

@BenCBanks wrote:

I am attempting to use this code (see below) to import multiple excel files (more than 100) that should all have the same file format.

---

You will likely be much better off saving all of these to CSV format and writing a data step to read them if they do have the same layout.

Proc Import guesses for each file such things as variable type, length and informat/formats and uses very few rows to "guess" what attributes to set.

Write a data step, or use proc import and a large value for the guessing rows value to generate a data step to read one, setting your desired variable names, lengths formats and informats. You can write a data step to read multiple files at one time, there are multiple examples on this forum.

[Tom]

VALIDVARNAME is for VARIABLE names.

VALIDMEMNAME is for MEMBER names.

options validvarname=any validmemname=extend;

[Kurt_Bremser]

Start by getting rid of the Excel files and have the data in text files (csv, tab-separated, fixed-width columns, whatever).

Once that is done, one data step with a wildcard (or a compound infile) will do all your work, and you will have total control over variable attributes.