Desktop productivity for business analysts and programmers

Simple report task not possible in EG? Need computed columns in a report

Reply
N/A
Posts: 0

Simple report task not possible in EG? Need computed columns in a report

Hi-

I'm relatively new to EG and have been quite frustrated that a particular and necessary reporting task is seemingly not possible in EG.

I'm trying to create a summary table and also include computed columns based on the summary statistics. For example, say I have the following dataset on calls made asking for donations. For each call, I record the donation made:

person_id donation
1 $1.00
1 $3.00
1 $4.00
2 $8.00
2 $6.00
2 $1.00
2 $2.00
2 $2.00
3 $1.00
3 $3.00
3 $4.00
3 $8.00
4 $6.00
4 $1.00
5 $2.00
6 $2.00
6 $1.00
6 $3.00

I want to calculate, by person_id, the average donation amount per call. This table would look like this:

user total_donations total_calls total_donations/total_calls
1 $7.00 3 $2.33
2 $19.00 5 $3.80
3 $16.00 4 $4.00
4 $7.00 2 $3.50
5 $2.00 1 $2.00
6 $6.00 3 $2.00

The last column is what is really bothering me. In EG I can create a summary table with number of calls (rows) and the sum of the donations for each user. What I cannot get it to do is to calculate the average donation/call, in the final column.

This is easy enough to do in SQL and seems to be a need for every report that I want to create. Many analytic professionals using EG must have to create a computed column in a summary report...how do you do it?

Can EG not do this? If not, how? Do I really have to edit the code directly for something as simple as this? I'd be very surprised if this was not a product feature of EG.

Thanks! I really appreciate any responses as I'm just getting started with SAS and hope to contribute to this forum at some point.

Cheers
-Scott
Trusted Advisor
Posts: 1,508

Re: Simple report task not possible in EG? Need computed columns in a report

Quote: ... a particular and necessary reporting task is seemingly not possible


This will always be a downside to menu-driven development environments: at one point, coding is the only way to access all features when you want total flexibility.

The code you want is compact enough, but menus to be able to generate all possible combinations of layouts would be extremely complex. In this case, the ratio of 2 columns might be a common enough requirement to warrant a menu entry. But then there is the next case, and so on...
[pre]
proc report;
column person_id n donation ratio;
define person_id / group ;
define N / 'Nb calls';
define donation / sum format=dollar10.2;
define RATIO / computed format=dollar10.2;
compute RATIO;
RATIO=_C3_/_C2_;
endcomp;
run;
quit;
SAS Employee
Posts: 149

Re: Simple report task not possible in EG? Need computed columns in a report

Hi Scott,

Welcome to EG and the forum! What you're describing is 100% doable within EG. I would suggest:

1. A Summary Statistics task wizard to calculate the total donations and total calls values. Have the wizard create an output data set.
2. A Query task, on the output data set from step 1, to create a computed column with the average donation per call. This also gives you a chance to select only the columns you want to report on since there will be others in the table from step 1.
3. A List Data task on the data set generated in step 2. This will allow you to format column values as needed, get the order of the columns the way you want etc.

These steps could be combined somewhat by using some of the advanced features of the Query task (aka Query Builder, aka Filter and Query) like summarized columns, computed columns, and the option to create a report directly from a query. The Query task writes PROC SQL code behind the scenes, so 95% of what you can do within ANSI standard SQL can be done point-and-click in the Query task. You can also write your own PROC SQL code if you feel more comfortable doing that.

In Enterprise Guide, be aware that tasks such as Summary Statistics or the Query Builder are often used to prep the data before using a reporting or analysis task. So, what you want many not be easily done using a single task, but might be a snap using two or more tasks one after another.

Richard
SAS Employee
Posts: 3

Re: Simple report task not possible in EG? Need computed columns in a report

Hi Scott,

There are 2 ways to do this in one task.

The first is using a filter and query task:
1) Select Filter and Query Task
2) Select id (with no summary type)
3) Select id and use summary type FREQ
4) Select amount and use summary type SUM
5) Select amount and use summary type AVG
6) Under Summary Groups, edit and group by id
7) Run

The second is using the Summary Table wizard task (can also be done using just the Summary table task):
1) Select Summary Table wizard task
2) in 2 of 6 - "Select analysis variable and statistics"
- add amount and select statistic "sum"
- add amount and select statistic "frequency"
- add amount and select statistic "average"
3) in 3 of 6 - "Select classification variables"
- add id in rows
4) Select Finish

Rob
Ask a Question
Discussion stats
  • 3 replies
  • 102 views
  • 0 likes
  • 4 in conversation