Importing CSV Files into SAS

[pw7632]

I am importing a folder of many csv files. All of them have three tabs but I only want to import the second and third tab. The problem is that all the tabs in the files have different names. What is the best way to approach this? Thanks. 

[PaigeMiller]

CSV files do not have tabs. Please explain further.

[pw7632]

Sorry excel files saved as csv.

[PaigeMiller]

Excel files saved as CSV do not have tabs.

[pw7632]

Sorry I am being pretty vague. Each file has three sheets, they are all excel files, but they are saved as a csv. 

[ballardw]

Reiterate: If you saved 3 tabs of an Excel file to CSV then you either have 1) 3 separate CSV files or 2) one corrupted file. The file format for CSV does not have "pages" , "tabs" or any similar subdivision. CSV are text files with rows of data separated by a comma (though some seem to use CSV for "character" separated , but they are all text files).

Do the tabs all have the same structure/layout in each Excel file?

Or in the case of separate Excel files did the tabs across files have similar structure (Tab A the same in each Excel file?).

[pw7632]

Im sorry they are excel files with three different sheets. There are three sheets in each file, I only want to get the contents of sheets two and three. Also, the files have different sheet names. The sheet name is pretty much the issue. Both the second and third sheets have the same format as each other as well as the same sheets across the different files. 

[SASKiwi]

Here is a link to the SAS documentation with example code. Please try it and then let us know how your got on: https://documentation.sas.com/?docsetId=proc&docsetTarget=n02nz0e7cykqhun14hcppfmd0558.htm&docsetVer...

[PaigeMiller]

---

@pw7632 wrote:

Im sorry they are excel files with three different sheets. 

---

Please go back and provide a correct title on the original post you made in this thread.

[Tom]

---

@pw7632 wrote:

Im sorry they are excel files with three different sheets. There are three sheets in each file, I only want to get the contents of sheets two and three. Also, the files have different sheet names. The sheet name is pretty much the issue. Both the second and third sheets have the same format as each other as well as the same sheets across the different files. 

---

Please provide a more concrete example of what you are dealing with.  For example you could list the names of two or three of the Excel files (clearly indicate if they are XLSX file or the older XLS format files) and the names of the sheets in those files.  Clearly indicate which ones you need to combine. How you know which ones you need to combine.  Note it will probably be difficult to do it based just on the order that the sheets appear in the Excel file (as opposed to using the names of the sheets in the Excel files).

[Patrick]

---

@pw7632 wrote:

Im sorry they are excel files with three different sheets. There are three sheets in each file, I only want to get the contents of sheets two and three. Also, the files have different sheet names. The sheet name is pretty much the issue. Both the second and third sheets have the same format as each other as well as the same sheets across the different files. 

---

To my knowledge SAS doesn't have a method implemented which lets you address Excel sheets by index/sheet number. That doesn't mean it can't be done but just that things become more complicated and that you will need to provide more and more specific information to help us help you.

As a first test please execute below code and let us know if that works. Make sure the Excel file is closed when executing the code.

%let path=<path to your Excel file>/<name of Excel file>.xlsx
libname myxlsx xlsx "&path";
proc contents data=myxlsx._all_;
run;

Questions

1. Is this a one-off task or something you must execute repeatedly for different sets of Excel Workbooks

2. How many Excel Workbooks are we talking about?

3. Is there anything in your tabs that make them unique like for example a column heading (variable name) or combination of variable names that only exist in a specific tab? It could also be a column that has always the same value - anything you can think of would help. If there is anything then that could be a rather simple way to identify the tabs in your Excel workbooks.

If there is nothing in the data that allows us to identify the tabs then we need to retrieve the Excel internal tab order from the workbook. 

In such a case next things we need to know:

- SAS version

- Operating system of your SAS server

- Is option XCMD set (or is it on NOXCMD)