The SAS Output Delivery System and reporting techniques

query with ODS - excel to pdf

Reply
Contributor
Posts: 22

query with ODS - excel to pdf

hi i need to create a report from an excel worksheet . I have to generate the report into a PDF file. Can someone tell me how i should go about it. Thank you
Contributor
Posts: 22

Re: query with ODS - excel to pdf

Hi its an excel file. i just hav to generate the exact same file into a pdf. PLS HELP
SAS Super FREQ
Posts: 8,743

Re: query with ODS - excel to pdf

Hi:
Generally speaking, SAS is best at reading the data from an Excel spreadsheet. So, for example, if you have a workbook/worksheet with sales data, SAS can read the tabular worksheet data into a SAS dataset. If your worksheet has embedded graphics, print headers, print footers, page numbers, etc, then those parts of the worksheet would NOT be part of the resulting SAS dataset.

There is a difference, in SAS terms between a dataset and a report. For example, reports have titles and page numbers. Datasets do not have page numbers. A report which displayed the dataset rows -would- have page numbers, but the data itself does not have page numbers.

So, you say you need to create a report from an Excel worksheet. I assume you are posting this question to the ODS forum because 1) you have access to SAS and 2) someone told you that ODS can create a PDF file (which is true).

In order to create a PDF file you would generally need this syntax:
[pre]
ods pdf file='c:\temp\myoutputfile.pdf';
......some SAS procedure that generates output.....
ods pdf close;
[/pre]

However, the ODS PDF code is really the LAST step in the process. In order to get your Excel worksheet data "usable" with SAS, you either have to:
Step 1:
1) use PROC IMPORT to import the worksheet into either a permanent or temporary SAS dataset
or
2) use the SAS Excel Libname engine to access the worksheet and either treat it as though it were a SAS dataset or create a temporary or permanent SAS dataset from the worksheet.

If you do not have SAS/Access for PC File Formats available to you to use either the #1 or the #2 approach, then you will need to follow the #3 approach which is:
3) Save your Excel worksheet as a CSV file and then read the CSV file with PROC IMPORT or with a DATA step program.

Then, once you have completed Step 1, you need to move onto Step 2:
Step 2:
1) decide on your procedure of choice for displaying or reporting on your data. This could be as simple as using PROC PRINT to display all the rows and all the columns from the data (imported from the worksheet) or you might want to find out some descriptive statistics, such as those produced by PROC UNIVARIATE or PROC MEANS. You might need to create customized column headers on your report -- which points to using PROC REPORT or you might want to "slice and dice" your data into more of a cross-tabular format, which points to using PROC TABULATE. Once you decide on your procedure of choice, and have working code, then you can proceed to step 3.

An example of a simple PROC PRINT example is:
[pre]
proc print data=sashelp.class;
title 'This is a display of all the rows in SASHELP.CLASS dataset';
run;
[/pre]

Except instead of using SASHELP.CLASS in the Step 2 code, you would use the name of the SAS dataset that you created in Step 1.

Then, in Step 3, you use ODS to create your PDF file, as shown above. So, the whole program flow would be something like this:
[pre]
** Step 1;
** Import step of some kind to import Excel worksheet;
** Call the output SAS dataset WORK.MYDATA;

** Step 2 and Step 3;
ods pdf file='c:\temp\myoutputfile.pdf';

proc print data=work.mydata;
title 'This is a display of all the rows in WORK.MYDATA dataset';
run;

ods pdf close;
[/pre]

cynthia
Contributor
Posts: 22

Re: query with ODS - excel to pdf

hi , i so have access to sas, now it is a excel file and how do i go about importing the excel sheet? You said that i can use the PROC IMPORT statement. Do you have some code for that? thank you so much for explaning it in detail
SAS Super FREQ
Posts: 8,743

Re: query with ODS - excel to pdf

Ask a Question
Discussion stats
  • 4 replies
  • 150 views
  • 0 likes
  • 2 in conversation