Different PROC SQL question

#1
Here's my code:

proc sql;
create table wavgpd as
select DISTINCT acctnum, sum(exp) as exp, avg (pd)
as PD, ((exp x pd) as WAVG_PD
from PD_MASTER
group by acctnum
order by acctnum;
quit;

I am trying to calculate an exp total, an average PD total and a
WAVG_PD for each acct num. Unfortunately, I get
duplicate acct numbers in my query, even with the
DISTINCT function in front of the acctnum variable.
What am I doing wrong? Any assistance would be greatly appreciated.
Thanks.
 

noetsi

Fortran must die
#2
If an acctnum has more than one pd you might have "duplicate" acctnum and the distinct command might not eliminate those. When this occurs you need to look at the rows you think are duplicate and see why they are occurring. I suspect when you do you will find some acctnum has more than one pd.
 
#3
I know that. I should have been clearer in my explanation. My solution was to
create a nested sub query that selected by DISTINCT account number with the calculated
fields created in the regular query.
 

noetsi

Fortran must die
#4
Could you send the full code including the subquery. And an example of the duplicates that are occurring? Its hard to comment on this without seeing the full query or the specific problem.

There are many ways to eliminate duplicates. The right way depends on what is causing the problem.