Sorry, I thought I was clear. The error is in the *data* on the second
line, the year in *my* test data is 2049 which is wrong, as you suggested
spurious years can occur in databases. When I executed your code it
company delay2002 delay2003
A 100.0% 66.67%
B 100.0% 25.00%
The percentage for company A year 2002 should have been 50% not 100%. When
I ran Toby's code on this data, I got
Obs company Year2002 Year2003 Year2049
1 A 100 66.6667 0
2 B 100 25.0000 .
This report is also wrong, but it has the distinct advantage that the
reader can see there is an error in the data for company A, and therefor
one has a chance to fix the data.
Programming for generality could not catch every error. For example, if
the year on the second line had been 2003 when it should have been 2002
there would have been nothing obvious in the report to show the error.
However, the original question was not about catching errors. I only
mentioned it because you claimed:
Well, the good side of my way is that you won't include the funky year
you don't want. It is not surprise to have year "2049" in many
I would much rather be surprised by the funky year in Toby's report than by
having a manager walk into my office and ask why I reported 100% delayed
flights for company A in year 2002 when the marketing department estimated
that 50% of the flights were on time in that year.
I agree that your code is not in error - on the correct data it produces
the correct report. In fact, I did not make that claim. I only claimed
that it was not an advantage to suppress dirty data in reports. Since you
offered, handling a bad year in the data base, as a benefit of your code, I
thought it important to show that it was a questionable benefit by running
with a dirty year that you had suggested.
Toby and DATA _NULL_ made an argument for coding for generality, based on
the issue of maintainability. After reading your claim, I wanted to show
that another argument, based on catching some data base errors, could also
Date: Fri, 16 Jun 2006 15:06:20 -0400
Reply-To: Wensui Liu <liuwensui@GMAIL.COM>
Sender: "SAS(r) Discussion"
From: Wensui Liu <liuwensui@GMAIL.COM>
Subject: Re: Percentage of subgroups
Comments: To: email@example.com
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
could you please point out my error in a more explicit way rather than
just saying 'it is wrong' or borrowing others' words?
I really appreciate it.
On 6/16/06, firstname.lastname@example.org <email@example.com> wrote:
> Several respondents provided answers to Maarten's question on how to
> calculate percentages. Wensui presented his code
> > Here is a more elegent solution in SQL:
> > proc sql;
> > create table
> > wensui as
> > select
> > company,
> > sum(case when year = 2002 and delay ~= . then 1 else 0 end)/
> > sum(case when year = 2002 then 1 else 0 end) as delay2002 format
> > = percent8.2,
> > sum(case when year = 2003 and delay ~= . then 1 else 0 end)/
> > sum(case when year = 2003 then 1 else 0 end) as delay2003 format
> > = percent8.2
> > from
> > test
> > group by
> > company;
> > quit;
> and then defended it with the statement
> >Well, the good side of my way is that you won't include the funky year
> >you don't want. It is not surprise to have year "2049" in many
> To test his assertion I ran his code with
> data test;
> input flight company $ year delay;
> 1 A 2002 13
> 2 A 2049 .
> 3 A 2003 5
> 4 A 2003 12
> 5 A 2003 .
> 6 B 2002 17
> 7 B 2002 28
> 8 B 2003 32
> 9 B 2003 .
> 10 B 2003 .
> 11 B 2003 .
> and found it produced
> company delay2002 delay2003
> A 100.0% 66.67%
> B 100.0% 25.00%
> This result is wrong, but with no hint that there is an error in the
> Either Toby or Yu's code would have pointed out the error. This gives
> another reason, in addition to maintenance, why generality can be
> Thanks to Doug for a simple TABULATE solution. I thought that was the
> way to go, but got miss lead by the other general approaches.
> Ian Whitlock
Senior Decision Support Analyst
Health Policy and Clinical Effectiveness
Cincinnati Children Hospital Medical Center