import multiple excel sheets in different files into SAS, append all files and get SAS output

[andreas_lds]

---

@Satori wrote:

I searched online and I am able to open one but when I try to open more than one it does not work.

The issue is that I don't know what I am doing; I am just copying the codes I find online and then I do not know how to adapt them to my situation

---

Start reading the documentation. Post the code you tried and explain what exactly did not work.

[RW9]

Sorry, I have provided you the information, and several ways of doing it.  Perhaps consider doing a course on SAS, and go through the SAS help videos:

https://video.sas.com/category/videos/how-to-tutorials

To learn how to use SAS.

[Tom · Accepted solution]

---

@Satori wrote:

What I am doing now is saving all the excel files as CSV files. Could you then tell me what code should I use to import this files to SAS and then stach them together and save it as a SAS file.

The file names are Data1, Data2, ..., Data104

Each file has now only one sheet since I deleted the other one to be able to save it as CSV.

---

Make sure you know the names of the files and that they are visible to SAS UE.  I doubt that a CSV file is name Data1 it is probably named Data1,csv. Perhaps whatever tool you are using to look at filenames is hiding the extension? Windows likes to do that. With SAS UE since it is running in a virtual machine you also need to find the full name for the file as seen by SAS.  You can use the files and folders explored in the left hand panel of the SAS/Studio interface.  If you right click and select properties on one of the files you can see the full filename.

Start by trying to read one of the files into a SAS dataset.  You could try using PROC IMPORT but it would be better to write your own data step to read it. Then you can control the variable names and the variables' attributes (type, length, formats attached, label etc) to make the individual dataset compatible. With PROC IMPORT each dataset's variables will be based on the particular data that SAS sees when it reads that file.  This can lead to some variables with different lengths or worse different types (number vs character).

Here is how I like to code a program to read a CSV with a data step.  

data data1 ;
  infile "/folders/myfolder/Data1.csv" dsd firstobs=2 truncover ;
  length Var1 8 Var2 $20 ;
  input Var1 Var2 ;
run;

So the first statement starts a data step. The value 'data1' is the name of the dataset to be created.

The second statement tells where to find the CSV file and how to parse it.  The physical name of the CSV file is the value in quotes (remember that SAS UE is running in a Linux virtual machine so file names as case sensitive.) The DSD option tells it that it is delimited file and also set DLM=','. Firstobs=2 tells it to skip the header row.  The TRUNCOVER option tells SAS to treat short lines as meaning the rest of the fields are missing.

The third statement defines your variables. Set all numeric variables to use length of 8 (SAS stores all numbers as floating point).  The $ before the length is what tells SAS that you want to define a character variable.  Set the length long enough to hold the longest string you expect.  Remember that dates and times are numeric variables.

The fourth statement is what actually reads the values from the line.  If you decide to add informat specifications in the INPUT statement remember to prefix them with the colon modifier so that SAS will continue to read the variables in list mode. 

The last statement ends the data step.

If you have dates or times then you will also want to add those to an INFORMAT and FORMAT statement. The first will tell SAS what informat to use to read the data and the second will tell it how to format the data so that humans can read it.  For example Excel might have written dates that look like '8-3-18'.  So you need to tell SAS whether to interpret that as MDY or DMY.   Personally I try to always display dates using YMD order to prevent that confusion.  So if you had a date variable named DATE you might add these two lines to your data step.

  informat date mmddyy. ;
  format date yymmdd10.;

Once you have been able to read one file then there are two methods to reading multiple files. One is to modify the data step to read them all into one dataset to start with.  If you can use a single wildcard to list all of the files then that is really easy. You just change the INFILE statement and add a little code to skip the multiple header rows.

data all_data;
  length fname $256;
  infile "/folders/myfolder/Data*.csv" dsd firstobs=1 truncover filename=fname;
  input @;
  if fname ne lag(fname) then input;
  length Var1 8 Var2 $20 ;
  input Var1 Var2 ;
run;

@so the extra lines of code (and modification to the INFILE statement) will create a variable with the name of the current file. The extra INPUT with the trailing @ will read a line but keep the line current for the real input statement below.  This will allow SAS to set the FNAME variable to name of the file it is currently reading. Then IF condition will be true for the first line of each file so that then the conditional INPUT statement will cause SAS to skip that header row.

The other method is read each CSV into its own SAS dataset.  You could then add a final data step with a SET statement that lists all of the individual files to combine them.