topic Re: Creat new tabs from in SAS Data Management

Creat new tabs from

ybz12003 — Fri, 26 May 2017 15:22:00 GMT

Hello:

I have a question. I have a CSV file contain 500 variables. I would like to regroup different variables into different tabs. For example, Variable1, Variable5, Variable18 in new tab1 in new CSV. Variable3, Variable7, Variable126 in new tab2 in new CSV, and so on. Please let me know if SAS could do this. Thanks.

Re: Creat new tabs from

RW9 — Fri, 26 May 2017 15:27:53 GMT

There is no such thing as "Tabs" in a CSV file. CSV=Comma Seperated Variable file and is plain text with row one having column headers, then each data item on separate rows.

What I think your talking about is Excel which does have tabs, but to do so you would need to save it as XLSX and lose all the benefits of the CSV file format. Why would you want to do this?

Re: Creat new tabs from

ybz12003 — Fri, 26 May 2017 15:33:19 GMT

Sorry, what I mean was exporting to different Sheets in the Excel format.

Re: Creat new tabs from

RW9 — Fri, 26 May 2017 15:35:01 GMT

If you have SAS 9.4 you can use libname excel statement to create native XLSX files, and then just copy over datasets keeping the variables you want in each.

If not, then you can export your data again using keep statements, but output to tagsets.excelxp and use the options to set sheet names on export.

Re: Creat new tabs from

ybz12003 — Fri, 26 May 2017 15:35:52 GMT

I have a Excel file with 500 columns, I like to regroup them into different sheets

Re: Creat new tabs from

ybz12003 — Fri, 26 May 2017 15:36:28 GMT

My is 9.3

Re: Creat new tabs from

RW9 — Fri, 26 May 2017 15:38:58 GMT

Without much information its hard to say. Probably easiest to highlight columns in Excel and copy and paste. If its a routine task, then get the data in CSV format, import the data, then export it again.

Re: Creat new tabs from

ybz12003 — Fri, 26 May 2017 15:40:39 GMT

Routine project, looking for SAS do the automation

Re: Creat new tabs from

ybz12003 — Fri, 26 May 2017 15:55:39 GMT

How to export them? Please advice.

Re: Creat new tabs from

ballardw — Fri, 26 May 2017 21:45:12 GMT

AFTER you have the csv imported into SAS then something like this may get you started. It creates an XML file that Excel can open and if desired save as XLSX. If ODS EXCEL works for then use XLSX for the file.

ods tagsets.excelxp file='path\filename.xml'
options (sheet_interval='PROC');

proc print data= have;
   var <your list of variable names that you want on the  
        tab goes here>;
run;

proc print data=have;
   var <your list of variable names that you want on the 
       second tab goes here>;
run;

proc print data=have;
   var <your list of variable names that you want on the 
       third tab goes here>;
run;
ods tagsets.excelxp close;

Re: Creat new tabs from

ybz12003 — Sat, 27 May 2017 00:52:29 GMT

How to label different name into different tabs? For example, tab1 name = text 1, tab2 name = text 2, tab3 name = text 3, etc.

What is the option "sheet_interval" below use for? I google "sheet_interval" could be Table, Page, Bygroup, Proc, or None. What are they refer to?

Re: Creat new tabs from

ballardw — Fri, 02 Jun 2017 15:12:44 GMT

@ybz12003 wrote:

How to label different name into different tabs? For example, tab1 name = text 1, tab2 name = text 2, tab3 name = text 3, etc.

You can use the option SHEET_NAME='Text' to specify the text for a tab and the name will have a counter appended for each sheet.

What is the option "sheet_interval" below use for? I google "sheet_interval" could be Table, Page, Bygroup, Proc, or None. What are they refer to?

Pretty much exactly what the option name says: what rule is used to create a new sheet. Since some procedures can create more than one table then you may want each table on a different sheet if so then use the 'TABLE' option. ByGroup requests a separate sheet for each level of a BY variable in a procedure using the BY statement. PROC means to place all of the output from one Procedure . NONE means all output is sent to the same sheet. PAGE means for those procedures that create their own "page" output then each page goes to a separate sheet.

The sheet_interval option can be changed while still writing to the same output file by inserting an addtion ODS EXCEL or ODS tagsets.excel statement referencing the desired option for the next output but not including a file name.

Re: Creat new tabs from

ybz12003 — Fri, 02 Jun 2017 16:45:58 GMT

Thank you so much for your valuable informantion.