ODS EXCEL OUTPUT

[deleted_user]

Hi,

Can anyone pls help me o get this output?
I am new to reporting task. This is the firs time I am using Proc Report.

NATURE DES SOINS Frais réels


AUXILIAIRES MEDICAUX AM 24,715
BIOLOGIE, RADIOLOGIE AR 39,879
CONSULTATIONS ET VISITES CV 75,488
CURES - MATERNITE - FRAIS D'OBSEQUES CM 0
HOSPITALISATION HC 45,488
PETITE CHIRURGIE PC 8,176
PHARMACIE PH 71,596
PRESTATIONS DIVERSES DI 12,575

DENTAIRE SD 89,977
. Soins Dentaires 17,001
. Protheses dentaires 46,628
. Orthodontie 25,398
. Implants 950

OPTIQUE OP 70,470
. Verres 48,539
. Montures 19,375
. Lentilles 2,526
. Forfait optique 30

TOTAL 438,364

The data is in 3 categories as shown in parts. Category is not displayed(NO DISPLAY)
Each category has groups (Capital letterrs),
and each group has some types (small leters)

The report is as shown above. For first category, there is anly one group and only one type. So only group is displayed. (the group and ype has same values).

But, for next two caegories,

Each grop has more than 2 types, and the value of type will come under Group (Indented ).

I have grouped the daa on category, group and type.

But I am not geing his outp.

Can anyone please help me out???

[deleted_user]

Sorry, a smal lcorrecion.


For first caegory, for each group, there is only one type. So, only grouup is dispalyed, but not type, because, grop and type has same value.

Bu, next 2 categories, same as said above......

[Cynthia_sas]

Hi:
It would be most useful to see your PROC REPORT code that you have tried, so we can see how your variables are named and how you are defining them in PROC REPORT.

It would also be useful to see your data -- not in the final report form, but in the structure of the SAS data set. You have shown us how you want the output report to be displayed. I am confused by the fact that all the other GROUP/TYPE values have numbers on the lline (such as HOSPITALISATION HC 45488 and Soins Dentaires 17,001 ) but the "NATURE DES SOINS Frais réels" report line does not show any numbers. I am envisioning that your data looks something like this...but possibly it is not already summarized and you want the summary numbers produced by PROC REPORT??
[pre]
cat grpvar type num
111 NATURE DES SOINS Frais réels 0
222 AUXILIAIRES MEDICAUX AM 24715
222 BIOLOGIE, RADIOLOGIE AR 39879
222 CONSULTATIONS ET VISITES CV 75488
222 CURES - MATERNITE - FRAIS D'OBSEQUES CM 0
222 HOSPITALISATION HC 45488
222 PETITE CHIRURGIE PC 8176
222 PHARMACIE PH 71596
222 PRESTATIONS DIVERSES DI 12575
333 DENTAIRE SD Soins Dentaires 17001
333 DENTAIRE SD Protheses dentaires 46628
333 DENTAIRE SD Orthodontie 25398
333 DENTAIRE SD Implants 950
444 OPTIQUE OP Verres 48539
444 OPTIQUE OP Montures 19375
444 OPTIQUE OP Lentilles 2526
444 OPTIQUE OP Forfait optique 30
[/pre]

Before you worry about the cosmetics of how the output should be formatted in ODS, it is more important to get the summarizing behavior that you want. For that, we need to see the code that you've tried and to understand a bit better the structure of the input data that PROC REPORT is dealing with.

cynthia

[deleted_user]

Hi Cynthia,


Sorry for the confusion caused.

The heading "NATURE DES SOINS" is a spanned header for first three columns(CATEG, GROUP & TYPE) , "Freis reels" is the next column header

I think my problem will be solved if i can do one thing.

i am summarizing the data for each category (at each category BREAK BEFORE Categ) so that I will get sumamry values at top.

But, tricky part is that,

For first category, the report dont need the summary values.
And remaining two categories have the summary values at top of each category.

Is there any way that I can make the first Category summary value doesnot come i nthe report???

[Cynthia_sas]

Hi:
I understand about the headers now. Thanks for clarifying.

About your other question: "Is there any way that I can make the first Category summary value does not come in the report??? "

The thing is that PROC REPORT, given a BREAK instruction, executes that break processing for EVERY group -- it doesn't matter whether there is 1 summarized report row or 10 summarized report rows underneath the summary line. So there's no way within PROC REPORT to suppress the summary line whose total is the same as the report row. PROC REPORT wants to treat every group the same as far as break processing goes.

For example, if you run the program below on SASHELP.CLASS, with a BREAK BEFORE to get the summary line above the other rows, you will see that AGE=16 has only 1 report row (the row for SEX=M) and the summary numbers on that report row are the same as the summary numbers on the summary line from the BREAK. The report output is shown below with added annotations:
[pre]
Age Sex Height Weight
11 54.4 67.75 <--- summary line from BREAK
11 F 51.3 50.5
M 57.5 85

12 59.44 94.4 <--- summary line from BREAK
12 F 58.05 80.75
M 60.366667 103.5

13 61.433333 88.666667 <--- summary line from BREAK
13 F 60.9 91
M 62.5 84

14 64.9 101.875 <--- summary line from BREAK
14 F 63.55 96.25
M 66.25 107.5

15 65.625 117.375 <--- summary line from BREAK
15 F 64.5 112.25
M 66.75 122.5

16 72 150 <--- summary line from BREAK
16 M 72 150
[/pre]

PROC REPORT doesn't care, as I said, whether there is 1 report row that goes with the summary or 10 report rows or 100 report rows. And you can see that behavior in the report above. EVERY group has a break line.

The only way that I know of to suppress lines, such as for age 16, would be for you to pre-summarize the data (either using PROC MEANS, or PROC TABULATE or the OUT= option with PROC REPORT) and then delete the rows that you consider extraneous. PROC REPORT is very nice in this regard, because if you create an output dataset with the OUT= option, a variable called _BREAK_ is included in the file that shows which report row came from a BREAK statement and which report row came directly from the data.

For example, if I look at the output dataset created by the above PROC REPORT code (with OUT= added), I can see that _BREAK_=Age when PROC REPORT inserted a row into the report because of break processing (and the COMPUTE AFTER is part of break processing). The output dataset from PROC REPORT (added annotations are mine):
[pre]
What is in DATASET created by REPORT

Obs Age Sex Height Weight _BREAK_

1 11 54.4000 67.750 Age <--BREAK BEFORE
2 11 F 51.3000 50.500
3 11 M 57.5000 85.000
4 11 54.4000 67.750 Age <--COMPUTE AFTER
-----------------------------------------------------------------------
5 12 59.4400 94.400 Age <--BREAK BEFORE
6 12 F 58.0500 80.750
7 12 M 60.3667 103.500
8 12 59.4400 94.400 Age <--COMPUTE AFTER
-----------------------------------------------------------------------
9 13 61.4333 88.667 Age <--BREAK BEFORE
10 13 F 60.9000 91.000
11 13 M 62.5000 84.000
12 13 61.4333 88.667 Age <--COMPUTE AFTER
-----------------------------------------------------------------------
13 14 64.9000 101.875 Age <--BREAK BEFORE
14 14 F 63.5500 96.250
15 14 M 66.2500 107.500
16 14 64.9000 101.875 Age <--COMPUTE AFTER
-----------------------------------------------------------------------
17 15 65.6250 117.375 Age <--BREAK BEFORE
18 15 F 64.5000 112.250
19 15 M 66.7500 122.500
20 15 65.6250 117.375 Age <--COMPUTE AFTER
-----------------------------------------------------------------------
21 16 72.0000 150.000 Age <--BREAK BEFORE
22 16 M 72.0000 150.000
23 16 72.0000 150.000 Age <--COMPUTE AFTER
-----------------------------------------------------------------------
[/pre]

If you added the N statistic to the report row, as a NOPRINT item, then you would know on the BREAK line, how many observations were collapsed into that report row for subsequent processing. In the code below, I added the N statistic to the report code below, so you could see how the output dataset from PROC REPORT might be processed.

Since I posted the code with the "pre-formatting" tags to preserve indenting, you will not be able to cut and paste this code directly into a SAS editor window. (well you CAN cut and paste directly, it just won't be very readable code). You will first have to cut and paste from the forum posting into an editor, such as Word, that respects the line feed characters; and then, cut and paste again from Word into your SAS session in order to run the code.

cynthia
[pre]
proc report data=sashelp.class nowd
out=classout;
column age sex height weight n;
define age /group;
define sex / group f=$3.;
define height/mean;
define weight/mean;
define n / 'count' noprint;
break before age / summarize;
compute after age;
line ' ';
endcomp;
run;

proc print data=classout;
title 'What is in DATASET created by REPORT';
run;
title;
[/pre]

[deleted_user]

Hi Cynthia,


Thanks a lot for the solution. That was really helpful as I am completely new to this procedure.

A small clarificaion needed as I am not in front of my machine.

Can we perform any manipulations on the PROC Report ..Output dataset??

I mean, edit, delete and insert the data??
And can I use the manipulated dataset again as the input for next Proc Report step or should I use only PROC PRINT?

[Cynthia_sas]

Hi:
The dataset created by PROC REPORT is just like any other SAS dataset. You can do whatever you want to the dataset in a DATA step program.

Then you could use either PROC PRINT or PROC REPORT to display the new version of the "adjusted" report dataset. If you use PROC REPORT, you would probably want to use DISPLAY usage for almost every report item because the summary lines will already be in the edited dataset.

cynthia

[Ksharp]

Hi.Cynthia.
I have a question for you.
Can I execute 'line' statement conditionally in compute block.
As far as I know,proc report does not have this ability.


Ksharp

[Tim_SAS]

Here is a SAS Note on this subject.

[Ksharp]

Thanks.Tim
That is a clever approach.


Ksharp