topic Re: Proc SQL: sum of distinct values in SAS Procedures

Proc SQL: sum of distinct values

HDSimo — Tue, 08 Feb 2011 10:59:00 GMT

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

Re: Proc SQL: sum of distinct values

Peter_C — Tue, 08 Feb 2011 11:19:11 GMT

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.

Re: Proc SQL: sum of distinct values

HDSimo — Tue, 08 Feb 2011 11:34:00 GMT

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

Re: Proc SQL: sum of distinct values

Olivier — Tue, 08 Feb 2011 11:43:12 GMT

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

Re: Proc SQL: sum of distinct values

HDSimo — Tue, 08 Feb 2011 11:47:09 GMT

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

Simone