Summary Tables - Exporting as separate files

[shudsoo]

Hi all,

I have a table of circa 3.5 million rows through which EG summary tables groups it by Hospital Provider and then produces me a page per different provider with the counts aggregated which is perfect.

What I need to do, somehow, is to get EG to then export the results on a separate page per provider  - there are about 450 providers (hospital providers)

So in summary - results go to a summary table with a page break per provider and I want those results to be exported as a separate file (PDF/CSV/XLS) by provider and not in one big PDF document/summary table report.

Any ideas...

[Cynthia_sas]

Hi:

  Using ODS is not a true "propietary" format export to Excel, since ODS makes ASCII text files that Excel knows how to open. But you can make a CSV file and/or a PDF file, as shown below.  NEWFILE=BYGROUP should work for you for most destinations to create a separate file for every BYGROUP. The files are numbered files, the first file has the name you specify in the FILE= option and then the file numbers increment from that starting point. Example below with SASHELP.SHOES.

cynthia

options nobyline nodate number pageno=1;

ods _all_ close;

ods csvall file='c:\temp\split1.csv' newfile=bygroup;

ods pdf file='c:\temp\split1.pdf' newfile=bygroup;

  title '#byval1';

  proc report data=sashelp.shoes nowd;

       by region;

       column product sales inventory returns;

       define product / group;

       rbreak after / summarize;

  run;

ods _all_ close;

options byline;

title;

[shudsoo]

Hi,

Thanks Cynthia, I have run the program and can see the results so I can probably work out how to repoint it at a suitable location etc and hook it up to the work table containing the results (or copying the code in from the EG process flow???) I think but how would I ensure that the filename for the csv contains the Provider (in my example) - could I use a parameter for that so that it would create a file containing the results by provider with the filename containing the providers name as well?

Thanks in advance

Regards

Steve

[Chuy]

Hi Shudsoo,

In the EG Summary Tables task, which produces Proc Template not Proc Print code, you can go to the Results section on the side panel

and check the box that says 'Save results to a data set' and select a location where you want to save it.  This will create a SAS data set with all the information you need. The data set will contain a variable called _PAGE_ (see screenshot), which you can use to your benefit. Write a program that will export out each of the desired pages in the format you want.

Hope this helps.

Post back if you need further assistance.

Chuy

[shudsoo]

Thanks Chuy,

I have got that coming out as well in the process flow as I have tried to import the results into DI to use a Loop to generate the csv but the results are not in the correct format to send to the customer (you know how they like things to look nice!) but thanks anyhow...

Rgds

S

[shudsoo]

Hi everyone ...

Right, I have managed to get the output that I require by inserting the piece of Cynthia's code as a start and end point (editing the code of the summary table) - see below....

This works and creates a .csv file for every instance of PROCODED and names the .csv files PROCODED1, PROCODE2, PROCODE3 etc which sort of solves the issue but what would be perfect would be if the filenames were the actual name of the PROCODED - IE 5PV.csv and 5NH.csv depending on the PROCODED from the table.

Is there a way in which to have the filename on the ODS dynamic to the table from which it is writing...?

Thanks

Steve

ods _all_ close;

ods csvall file="\\IC.Green.Net\IC_User_DFS\PDrive\SHudsoo\S Hudson Documents\Test\PROCODED.csv"

newfile=TABLE;

PROC TABULATE

DATA=WORK.QUERY_FOR_SHDQ_APC_DUPL_TBL_0000

     OUT=WORK.STABSummaryTablesQUERY_FOR_SHDQ_(LABEL="Summary Tables  for WORK.QUERY_FOR_SHDQ_APC_DUPL_TBL_0000")

    ;

   VAR Calculation;

     CLASS EPISODE_END_GROUP /  ORDER=UNFORMATTED MISSING;

     CLASS SUBDATE / ORDER=UNFORMATTED MISSING;

     CLASS PROCODED /     ORDER=UNFORMATTED MISSING;

     TABLE /* Page Dimension */

PROCODED,

/* Row Dimension */

SUBDATE*

  N

ALL={LABEL='Total (ALL)'}*

  N,

/* Column Dimension */

EPISODE_END_GROUP

ALL={LABEL='Total (ALL)'}       ;

     ;

RUN;

ods _all_ close;

/* -------------------------------------------------------------------

   End of task code.

   ------------------------------------------------------------------- */

RUN; QUIT;

TITLE; FOOTNOTE;

[Cynthia_sas]

Hi:

  There is no "option" or "dynamic" way (using ODS techniques) to override the automatic name created from NEWFILE=. You always get the numbered file names with ODS.

  You could "macro-tize" your code and run PROC TABULATE for each group. In that case, you could make the FILE= option include a macro variable that held the name you wanted each group to have. But in this case, your ODS CSV step would be invoked via macro program for each group. So to do what you want involves a learning curve for SAS Macro Language.

  It might be easier, depending on your constraints, to get the automatic names and then write command file to rename them.

cynthia

[Chuy]

Shudsoo,

You stated:  Is there a way in which to have the filename on the ODS dynamic to the table from which it is writing...?

This is why I said to use the generated data set, you have more flexibility now that you have the data in the form you needed.

Create a macro, pass it parameters, such as using the _PAGE_ and/or the variable name in WHERE= clause in the data set option

with the specified value in a macro string.

good luck.

[shudsoo]

Thanks Chuy...

I can get to generated Dataset - say I wanted to generate a .csv per provider (PROCODED) which is on every page. What is the macro language syntax as I have never done that before.

Thanks in advance...

Rgds

Steve