How to Display text in rows using proc report for create cover page.

[Aquaroyal72]

Can any one know how to create cover page in excel, using proc report or proc tabulate.

text should be come in row format...

Text1 long string

Text2 long string

text3 long string

Text4 long string

and also want to know how you can how to use substr() with this.

Please help......

[Cynthia_sas]

Hi:

  How are you creating output for Excel? ODS or the LIBNAME engine or PROC EXPORT? You are probably using ODS, but then which ODS destination? ODS CSV, ODS HTML, ODS MSOFFICE2K or ODS TAGSETS.EXCELXP? Usually, the request that I get for a cover/title page is with the creation of PDF or RTF results. I don't get many questions about a cover page for Excel, because if Excel opens a CSV file, for example, or an HTML file, then everything is in one worksheet in the workbook and there are no real "page" breaks. So the concept of a cover page or title page is irrelevant for those destinations. For TAGSETS.EXCELXP, you can probably get a separate sheet for your "cover" page -- but it would really be a separate sheet in the workbook.
  
  PROC TABULATE will only create tabular results. So it would not be a good choice for creating a title page or cover page. You would have to use PROC REPORT for your cover/title page. I'm not sure what you mean by using the SUBSTR function. Do you have any code that you've tried or example that you could post?
 
  These previous forum postings did show some examples of a title page:
https://communities.sas.com/message/117352#117352
https://communities.sas.com/message/14679#14679
  
cynthia

[Aquaroyal72]

Hi Cynthia,

Thanks for the code....I want to know any way you can create excel workbook(create tabs). the excel Template(cover letter and specification) is already there, just to insert the data in seperate tabs. Is that any way we can do it in SAS code.

If you have any example or code, can you please share the logic, I will greatly appericiated.

Thank you,

[Cynthia_sas]

Hi:

  Is this a new question or is this related to your first question? You did not explain HOW you are creating output for Excel, right now. Are you using ODS with PROC REPORT or PROC TABULATE? Or, are you doing an EXPORT using PROC EXPORT? Or...

  Creating a cover or title page is a different question than how to populate a worksheet in an existing workbook, or creating a new worksheet in an existing workbook.

  When you use ODS, ODS cannot add information to an existing workbook. ODS writes or rewrites over the entire file.

  The only way using SAS methods to add a sheet to an existing workbook is to use PROC EXPORT or the SAS LIBNAME engine for Excel -- of course with those 2 methods, you are exporting "data" and there is no "extra" formatting (like fonts or colors or styles) in what you export. So in that case, nothing from the cover page examples would work with PROC EXPORT or the LIBNAME engine.

  If you use non-SAS techniques, such as DDE, ODBC or OLE-DB or VB or VBA or .NET/.COM you can add information to an existing workbook.

  You might want to open a track with Tech Support to see if they can point you in the right direction. If your goal is to add a worksheet to an existing workbook, then that is not something you can do "out of the box" with ODS.

cynthia

[Aquaroyal72]

Hi,

Thank you, I'm trying to create both ways, as my managers ask me to do it. The are doing the cover sheet and specification sheet manually. now they try to automate it so one person feed the data and report will comeout in wookbook format, with cover sheet and specificationpage included.

You understand what I mean.

can you help me in this one. One of your code, which help me to create the coversheet.

But the cover sheet is so fancy and they have different customer they create different report and create new coversheet, thats the problem. I create template with coversheet and specification for each customer. Once If they want for this cust1 the report 1 will print with template1, cust2 the report2 will printed on template2 and so on.....

I'm Using SAS....

can you help me...Please

you understand what I mean.

Shailesh Patel

[Cynthia_sas]

Hi:

  I do not know what you mean. When you say that you are trying to "create both ways" and that they are doing the cover sheet and specification sheet manually, you still have not explained what "both ways" means. You have not shown any SAS code. Are you trying to use:

-- PROC EXPORT and the SAS LIBNAME engine for Excel or

-- PROC EXPORT and ODS or

-- ODS and the LIBNAME engine for Excel or

-- ODS for the cover page and an existing workbook for the data

-- ODS and PROC REPORT for the cover page and then use ODS and PROC TABULATE for the report

-- your existing Excel template and add data from SAS to the template (nothing about ODS in this scenario)

-- ODS/SAS procedure  to create the report for Excel and then manually do the cover sheet

-- ODS/SAS to create the report for Excel AND create the cover page so both the report and the cover page are different for each customer

"create both ways" can be interpreted to mean conflicting possibilities that I mentioned and I just listed quite a few ways that the phrase could be interpreted. And, further, if you are using ODS to create output for Excel, which ODS destination are you using: ODS CSV, ODS HTML, ODS MSOFFICE2K, ODS TAGSETS.EXCELXP?? Again, more than 2 possibilities, so I don't know what you mean by "both ways".

  It sounds to me like you have designed an Excel-specific solution when you say that you "create template with coversheet and specification for each customer" --is this in an Excel template? One template for all customers? or One template for each customer?

  As I explained in my previous post: ODS will not write to an existing Excel file (no matter whether the file is a simple Excel worksheet or an Excel template file).

  I do understand, from your explanation, that Customer 1 has one type of cover page and that Customer 2 has another type of cover page. That would not be a problem for SAS to create different cover page text or formatting for one customer versus another. The examples that have been posted all show the same cover page for a report or group of reports. Using SAS Macro coding -- either macro variables or a macro program definition, you could generate different text for different customers. However, it doesn't make sense to me to go down the Macro road as a possibility until there is more clarity about what your existing code is and what your data looks like and what your existing process is and how you want to create your output .

  I would really recommend that you open a track with Tech Support. They can look at all your data and your existing SAS code and help you figure out which method (PROC EXPORT) or the LIBNAME engine or ODS would be most appropriate for what you need to do.

cynthia