SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creat new tabs from

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

Creat new tabs from

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. 


Accepted Solutions
Solution
‎06-01-2017 10:34 PM
Super User
Posts: 11,343

Re: Creat new tabs from

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,942

Re: Creat new tabs from

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?

Super Contributor
Posts: 319

Re: Creat new tabs from

Sorry, what I mean was exporting to different Sheets in the Excel format.
Super User
Super User
Posts: 7,942

Re: Creat new tabs from

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.

Super Contributor
Posts: 319

Re: Creat new tabs from

I have a Excel file with 500 columns, I like to regroup them into different sheets
Super User
Super User
Posts: 7,942

Re: Creat new tabs from

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.

Super Contributor
Posts: 319

Re: Creat new tabs from

How to export them?  Please advice.

Super Contributor
Posts: 319

Re: Creat new tabs from

My is 9.3
Super Contributor
Posts: 319

Re: Creat new tabs from

Routine project, looking for SAS do the automation
Solution
‎06-01-2017 10:34 PM
Super User
Posts: 11,343

Re: Creat new tabs from

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;
Super Contributor
Posts: 319

Re: Creat new tabs from

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?

Super User
Posts: 11,343

Re: Creat new tabs from


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.

 

Super Contributor
Posts: 319

Re: Creat new tabs from

Thank you so much for your valuable informantion.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 272 views
  • 2 likes
  • 3 in conversation