topic Re: Create different XLS files named after the groups in the BY statement in New SAS User

Create different XLS files named after the groups in the BY statement

Rafa — Tue, 30 Oct 2018 19:29:54 GMT

Dear all,

Hope you're doing OK 🙂

I come to you because of the following: I need to generate a different XLS file for each group in a BY statement.

Also important: the name of each file should be the corresponding category from the BY statement.

As of now, I'm working with the following code:

    ods tagsets.excelxp file='multisheet.xls' style=statistical
      options( sheet_interval='bygroup' );

        proc sort data=PRUEBA_COM out=class;
     by attribute_4;
  run;

  proc print data=class;
     by attribute_4;
  run;

  ods tagsets.excelxp close;

(Code obtained from: https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data)

That code gives me a sheet from each BY group (which is fine) and the name of each sheet is "attribute_4=xxxx" (which is also fine).

The thing is they all are in a single file ("multisheet.xls"). So, my questions are as follows:

1) Do you know of any other way to generate single XLS files and name them after their BY category?

2) If not, is there a way to re-import this Excel file ("multisheet") and split the sheets afterwards in different XLS files named after each sheet's name?

Thank you!

Re: Create different XLS files named after the groups in the BY statement

Reeza — Tue, 30 Oct 2018 20:42:54 GMT

1. Not easily, a macro or call execute is usually the fastest way

2. No. ODS TAGSETS genreates an XML file, not an actual xls file so SAS cannot reimport the data easily. If you convert it to a native XLS file then it can be reimported.

If you have SAS 9.4 TS1M3+ you can generate native xlsx files.

Re: Create different XLS files named after the groups in the BY statement

ChrisHemedinger — Tue, 30 Oct 2018 22:28:26 GMT

As @Reeza says, you'' probably need to wrap your logic in a simple SAS macro. But don't be intimidated -- you can do it!

Start simple, getting the program to work for one group. Then slowly add the macro logic around your process until you've got it all working. Here's an example (with steps) that you can follow here.