BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

12 REPLIES 12
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

ybz12003
Rhodochrosite | Level 12
Sorry, what I mean was exporting to different Sheets in the Excel format.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ybz12003
Rhodochrosite | Level 12
I have a Excel file with 500 columns, I like to regroup them into different sheets
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ybz12003
Rhodochrosite | Level 12

How to export them?  Please advice.

ybz12003
Rhodochrosite | Level 12
My is 9.3
ybz12003
Rhodochrosite | Level 12
Routine project, looking for SAS do the automation
ballardw
Super User

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;
ybz12003
Rhodochrosite | Level 12

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?

ballardw
Super User

@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.

 

ybz12003
Rhodochrosite | Level 12

Thank you so much for your valuable informantion.

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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