Multi sheet csv file

[GreggB]

I have the following code to export a PROC tabulate to a csv file.  It works fine.  However, I want to execute 3 separate tabulates and export them into 1 csv file with each tabulate result in a different sheet, rather than creating three different csv files. Is there a SHEET= option that will allow me to do this?

ODS csv file='G:\Departments\Research\MAP\1415\Longitudinal Match\long_match_math.csv' ;

proc tabulate data=join_long;

...

.....

title'math results';

run;

ods csv close;

[stat_sas]

Not sure about ODS csv but you can try something this

ODS TAGSETS.EXCELXP

FILE='G:\Departments\Research\MAP\1415\Longitudinal Match\long_match_math.xls';

STYLE=minimal

OPTIONS ( Sheet_Name = 'Shoes' );

PROC PRINT DATA=sashelp.shoes; RUN;

ODS TAGSETS.EXCELXP

OPTIONS ( Sheet_Name = 'Class' );

PROC PRINT DATA=sashelp.class; RUN;

ODS TAGSETS.EXCELXP CLOSE;

[Cynthia_sas]

HI: I agree with STAT@SAS. If you need multiple sheets, then TAGSETS.EXCELXP is a better choice. The fact is that with a CSV file, there is no way to create multiple sheets other the LIBNAME engine or PROC EXPORT -- which won't work with TABULATE. All the output from ODS CSV or ODS CSVALL goes to 1 sheet, which you can prove to yourself by running the attached code.

Cynthia

ods _all_ close;

ods csvall file='c:\temp\_3_tabulate_steps.csv';

proc tabulate data=sashelp.class;

title '1) TABULATE for CLASS';

  class age;

  table age all,

        n pctn;

run;

proc tabulate data=sashelp.cars;

title '2) TABULATE for CARS';

  class type;

  table type all,

        n pctn;

run;

proc tabulate data=sashelp.shoes;

title '3) TABULATE for SHOES';

  class product;

  table product all,

        n pctn;

run;

ods csvall close;

title;

[art297]

: While you can't create a csv file with multiple sheets, you can create a workbook that contains a macro that, when clicked, will create a multi-sheet workbook from the csv files.

I stole the following code/method from a post at: Import multiple csv files into current workbook as separate sheets [SOLVED]

If you place all of your csv files in one directory, create a workbook, insert a text box with some instruction (e.g., click here to view the files) and assign the following macro to it:

Sub LoopFiles()
  Dim strDir As String, strFileName As String
  Dim wbSourceBook As Workbook
  Dim wbWriteBook As Workbook
  Dim wsWriteSheet As Worksheet

  strDir = "C:\myCSVfiles\" 'specify folder to search
  strFileName = Dir(strDir & "*.csv")

  Set wbWriteBook = Workbooks.Add

  Do While strFileName <> ""
  Set wbSourceBook = Workbooks.Open(strDir & strFileName)
  Set wsWriteSheet = wbWriteBook.Sheets.Add
  wsWriteSheet.Name = strFileName
  wbSourceBook.Sheets(1).UsedRange.Copy wsWriteSheet.Range("A1")
  wbSourceBook.Close False
  strFileName = Dir
  Loop

End Sub

HTH,

Art

[ScottBass]

There aren't "multiple sheets" in a CSV file.  So, what is it you want to do?  Do you want to create multiple CSV files, then import them as multiple worksheets in an Excel workbook?  Or something else?

You can use ODS Excel tagsets to create an Excel workbook (in somewhat bloated XML format) that contains multiple worksheets.

[jakarman]

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...