topic Re: Summing across in proc sql in SAS Procedures

Summing across in proc sql

mdbenson — Mon, 10 Feb 2014 05:52:38 GMT

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;

Re: Summing across in proc sql

LinusH — Mon, 10 Feb 2014 10:23:58 GMT

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).

Re: Summing across in proc sql

Patrick_Tan — Mon, 10 Feb 2014 10:33:02 GMT

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

Re: Summing across in proc sql

mdbenson — Mon, 10 Feb 2014 13:24:58 GMT

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!

Re: Summing across in proc sql

mdbenson — Mon, 10 Feb 2014 13:26:30 GMT

Thanks Patrick_Tan. I'll add that.