Importing data from multiple excel files with multiple sheets

[kamal1974]

Hi everyone, I a using the trial version of SAS Studio I got this code with a help from ChatGPT, however I still getting these errors, I do appreciate any support here. Thank you

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
/* Set the path to your folder */
%let folder_path = 'C:/Import/';

/* Get a list of all Excel files in the folder */
filename excel_files pipe 'dir "C:/Import/*.xlsx" /b';

/* Import data from each Excel file in the folder */
data all_data;
   length filename sheetname $200.;
   infile excel_files truncover;
   input filename $;
   
   /* Set the sheet names you want to import from each file */
   sheetname1 = 'Sheet1';
   sheetname2 = 'Sheet2';
   sheetname3 = 'Sheet3';
   
   /* Import data from each sheet in the Excel file */
   do i=1 to 3;
      sheetname= 'sheetname'||put(i,1.);
      if sheetname1 ne '' then do;
         if i=1 then call execute('proc import datafile="'||"&folder_path."||'\'||filename||'"
            out=work.'||compress(filename)||'_s'||compress(i)||' dbms=xlsx replace;
            sheet="'||sheetname1||'"; run;');
         else call execute('proc import datafile="'||"&folder_path."||'\'||filename||'"
            out=work.'||compress(filename)||'_s'||compress(i)||' dbms=xlsx replace;
            sheet="'||strip(sheetname)||'"; run;');
      end;
   end;




LOG:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

68

69 /* Set the path to your folder */

70 %let folder_path = 'C:/Import/';

71

72 /* Get a list of all Excel files in the folder */

73 filename excel_files pipe 'dir "C:/Import/*.xlsx" /b';

ERROR: Invalid logical name.

ERROR: Error in the FILENAME statement.

74

75 /* Import data from each Excel file in the folder */

76 data all_data;

77 length filename sheetname $200.;

78 infile excel_files truncover;

79 input filename $;

80

81 /* Set the sheet names you want to import from each file */

82 sheetname1 = 'Sheet1';

83 sheetname2 = 'Sheet2';

84 sheetname3 = 'Sheet3';

85

86 /* Import data from each sheet in the Excel file */

87 do i=1 to 3;

88 sheetname= 'sheetname'||put(i,1.);

89 if sheetname1 ne '' then do;

90 if i=1 then call execute('proc import datafile="'||"&folder_path."||'\'||filename||'"

91 out=work.'||compress(filename)||'_s'||compress(i)||' dbms=xlsx replace;

92 sheet="'||sheetname1||'"; run;');

93 else call execute('proc import datafile="'||"&folder_path."||'\'||filename||'"

94 out=work.'||compress(filename)||'_s'||compress(i)||' dbms=xlsx replace;

95 sheet="'||strip(sheetname)||'"; run;');

96 end;

97 end;

98

99 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/EXCEL_FILES.

WARNING: The data set WORK.ALL_DATA may be incomplete. When this step was stopped there were 0 observations and 6 variables.

WARNING: Data set WORK.ALL_DATA was not replaced because this step was stopped.

109

[ballardw]

CHATGPT is too stupid to know that any LIBNAME or FILENAME reference is limited to 8 characters.

filename excel_files pipe 'dir "C:/Import/*.xlsx" /b';

The text highlighted above is the reference and must be reduced to 8 characters and cannot use any characters other than letters, digits and the underscore character  and may not start with a digit. Everyplace that is referenced needs to be changed to the same 8 or fewer character reference.

Since the INFILE statement fails to meet syntax the rest of the data step basically wasn't even checked.

You will also have a failure here:

         if i=1 then call execute('proc import datafile="'||"&folder_path."||'\'||filename||'"

Your defined macro variable (did you write that or Chatgpt?) folder_path was defined with quote characters with this:

%let folder_path = 'C:/Import/';

So the quotes will be placed in the middle of the path that is supposedly created for the datafile. Which will fail because that line

will generate :

'proc import datafile="'C:/import/'\<whatever the file is eventually read>" 

So the single quotes cause one problem, and after those are removed you still have an issue with getting rid of adjacent /\ characters.

Not sure why you are mixing Unix folder delimiters, /, with Windows, \, but pick one and make sure that they aren't adjacent.

This problem with the macro variable occurs twice.

Chatgpt also has apparently never heard of the functions CATS and CATX that can reduce the repeated inclusion of "||compress(variable)||" calls (and if there are actually needed blanks in the middle of the such as poorly named sheets compress("My sheet 3") for file name would become "Mysheet3" and then not found in the spreadsheet,

compress will remove them causing likely runtime errors).

You also really should check that the data step name created will have a length of 32 or fewer characters. If someone had a real long sheet name your addition of _S1 may exceed the limits of a SAS data set name. Consider a sheet name like "This_SHEETNAME_with_exactly_30". Attempting to add _s1 will take the length to 33 and generate errors.

If you expect to combine any of these resulting data sets later you may have another shock when variables of the same name have different types and/or lengths.

I'm sure there are other issues yet to come.

[novinosrin]

@ballardw  Never lost your sense of humor. Rather, yours gets better with age 🙂 I hope you're doing well. I am not so active here but always enjoyed reading your posts 🙂

[Kurt_Bremser]

If, by "trial version", you mean SAS On Demand for Academics (or any other server-based SAS installation), you cannot access your desktop drives from code. You have to upload files to the server first (Studio provides a button for this) before you can read them.

Also, FILENAME PIPE will not work on On Demand.

[kamal1974]

Thank you @Kurt_Bremser  for your kind reply, I think you are right, I did upload the files as you can see in the attached screenshots. Now how to use the new server path for the files?

[Kurt_Bremser]

Since you cannot use external commands, you need to build the directory listing with the FILENAME, DOPEN, DNUM and DREAD functions. The path on the UNIX system can be found by right-clicking on the folder.

You can find an example in my WUSS presentation 

[kamal1974]

FYI, I am using Windows not Unix, in fact I am trying to find which tool is the best for this job among VBA, SAS & Python in terms of handling very complex data and performance. Is there anyway to install a local copy of SAS or should I keep trying on the server trial version? What do you recommend please & thank you so much for the support.

[SASKiwi]

SAS On Demand for Academics runs on a remote Unix (Linux) server so that is why it is relevant to your server folder names. SODA is for learning SAS and has data and processing limits. It is not appropriate for large data volumes and processing requirements. There is SAS Viya on Azure Marketplace which has a pay as you go charging model. This is more scalable but does cost money. 

[Kurt_Bremser]

---

@kamal1974 wrote:
FYI, I am using Windows not Unix, 

---

Your desktop runs on Windows, but your SAS session runs on UNIX, as can be seen by this path (quote from your initial post):

ERROR: Physical file does not exist, /pbr/biconfig/940/Lev1/SASApp/EXCEL_FILES

(no drive letter, forward instead of backward slashes)