Summing across in proc sql

[mdbenson]

Hi,

I'm very new to SAS and I've written this code and it bombs out.  Could any of you experts show me there errors of my way?  I'm trying to get the average, max and count (some quarters are missing) for 4 quarters of data.  Also, in the WHERE statement at the bottom, I'm trying to subset where the Recipients were the 3 listed??  Can I use the ||  to mean OR?  I thought I read you could.  Any help would be great!

proc sql;

  select Employee_ID 'Employee ID',

  (sum(Qtr1, Qtr2, Qtr3, Qtr4)/count(Qtr1, Qtr2, Qtr3, Qtr4)) as avg_don 'Average Donation',

  max(Qtr1, Qtr2, Qtr3, Qtr4) as max_don 'Maximum Donation',

  count(Qtr1, Qtr2, Qtr3, Qtr4) as qtr_don 'Quarters Donated'

  from sqldata.employee_donations

  where (find(Recipients, "Bob" || "Kevin" || "Ashely", "i") > 0);

quit;

[LinusH]

What dos your source data look like, and what is the desired result?

As for the OR operator, a single pipe is used in SAS. || means character concatenation of the two expressions surrounding it.

SAS(R) 9.4 Language Reference: Concepts, Second Edition

Be sure that you don't confuse SQL aggregation functions (works over columns) and the corresponding SAS functions (works on a row level).

[mdbenson]

The original data has Employee ID, quarterly donations made (Qtr1, Qtr2, Qtr3, Qtr4), and Recipients of the donations (I just changed those to peoples name to be more generic.  They can be the name of any 3 charities).  I want to find the avg donation, max donation, and the count (that is, how many quarters did each Employee donate).  Thanks for the info on the OR that I was trying to perform.  That was meant to act as a filter to just find data on certain charities.

Thanks!

[Patrick_Tan]

Asuming you want to calculate the statistics per employee I am missing the "group by Employee_ID" statement.

[mdbenson]

Thanks Patrick_Tan.  I'll add that.