Help using Base SAS procedures

Proc SQL: sum of distinct values

Reply
Occasional Contributor
Posts: 19

Proc SQL: sum of distinct values

Hello,
with Proc SQL is possible to have the SUM of distinct values of a specific variable. I need an option in this Proc that sum values of a variable referring to different values of another variable. Another way could be the proc sort nodupkey and then the proc freq.

Thank you

Simone
Valued Guide
Posts: 2,177

Re: Proc SQL: sum of distinct values

when selecting the set of distinct values, or when using the nodupkeys option of proc sort, how do you decide which row to keep or which rows to drop?

I would be concerned that neither method defines which of "the top equal salesmen" (for example) would be chosen to be included on the input to your final summary.
Occasional Contributor
Posts: 19

Re: Proc SQL: sum of distinct values

Hi Peter,
each row that i drop has the same values of the row that I keep. Let me show you a situation similar to my database:
variable ID identify a person
variable X specify a weight

In the database there are a lot of duplicated record, It's not important wich rows i've deleted. I need to calculate the sum of x but i can't use X as distinct values option because identical values of X are linked to different ID.

Thank you

Simone
Super Contributor
Posts: 260

Re: Proc SQL: sum of distinct values

Hi Simone.
Maybe a subquery would help :
[pre]PROC SQL ;
SELECT SUM(x) FROM (
SELECT DISTINCT id, x FROM myData
);
QUIT ;[/pre]
This is very similar to executing a PROC SORT with the NODUPRECS option prior to summing.
Hope this helps.
Olivier Message was edited by: Olivier
Occasional Contributor
Posts: 19

Re: Proc SQL: sum of distinct values

mmm... simple!
Thank you very much Olivier!

Simone
Ask a Question
Discussion stats
  • 4 replies
  • 4171 views
  • 0 likes
  • 3 in conversation