BookmarkSubscribeRSS Feed
GreggB
Pyrite | Level 9

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;

5 REPLIES 5
stat_sas
Ammonite | Level 13

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
SAS Super FREQ

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
Opal | Level 21

: 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
Rhodochrosite | Level 12

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.


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 9376 views
  • 0 likes
  • 6 in conversation