topic Re: Multi sheet csv file in SAS Procedures

Multi sheet csv file

GreggB — Fri, 29 Aug 2014 19:19:07 GMT

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;

Re: Multi sheet csv file

stat_sas — Fri, 29 Aug 2014 19:55:37 GMT

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;

Re: Multi sheet csv file

Cynthia_sas — Fri, 29 Aug 2014 22:53:10 GMT

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;

Re: Multi sheet csv file

art297 — Sat, 30 Aug 2014 16:03:30 GMT

: 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

Re: Multi sheet csv file

ScottBass — Mon, 01 Sep 2014 00:44:54 GMT

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.

Re: Multi sheet csv file

jakarman — Mon, 01 Sep 2014 06:30:33 GMT

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