BookmarkSubscribeRSS Feed
Aquaroyal72
Calcite | Level 5

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

5 REPLIES 5
Cynthia_sas
SAS Super FREQ

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
Calcite | Level 5

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
SAS Super FREQ

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
Calcite | Level 5

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
SAS Super FREQ

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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 929 views
  • 7 likes
  • 2 in conversation