Proc SQL Conditional sum Across Row and Column

[sachin01663]

Hello,

Can I do this with Proc SQL. I have four variable (Name  - to -  Type_of_Complaint) and want to populate Agg_anti, Agg_Light variables, Total_Complaints and Sum_Salary

The data should be aggregated up to 'Name' level. 

Agg_Anti ~ should contain Number of 'Anti' complaints for each person (Name) from 'Type_of_Complaint' column

Agg_Light ~ should contain Number of 'Light' complaints for each person (Name) from 'Type_of_Complaint' column

Total_Complaints ~ Agg_Anti + Agg_Light (summing Across Column)

Sum_Salary ~ should contain Sum of 'Salary' (again at Name level)

Name	Salary	other earnings	Type_of Complaints	Agg Anti	Agg Light
Sachin	10	2	anti
Suraj	15	3	Light
Neha	20	anti
Mishu	4	Light
Sapan	17	5	anti
Sachin	5	6	Light
Suraj	2	3	anti
Suraj	15	Light
Neha	20	5
Mishu	5	Light
Sapan	17	2	anti
Sachin	5	1	Anti

I have done this in two steps a) SAS data step and b) Proc SQL howvere want to do this in one step.

Thanks

sachin

[art297]

Your data table appears to have pasted incorrectly.  Can you provide your data in the form of a datastep?  Plus, seeing how you got what you want in a datastep would also clarify what you are trying to accomplish.

[PGStats · Accepted solution]

Sachin, is this what you want:

data have;
infile datalines truncover;
input Name $ Salary other_earnings Type_of_Complaints $;
datalines;
Sachin 10 2 anti
Suraj 15 3 Light
Neha 20 . anti
Mishu 4 . Light
Sapan 17 5 anti
Sachin 5 6 Light
Suraj 2 3 anti
Suraj 15 . Light
Neha 20 5
Mishu 5 . Light
Sapan 17 2 anti
Sachin 5 1 Anti
;

proc sql;
create table want as
select name, sum(salary) as sum_salary,
sum(upcase(type_of_complaints)="LIGHT") as agg_light,
sum(upcase(type_of_complaints)="ANTI") as agg_anti,
calculated agg_light + calculated agg_anti as Total_Complaints
from have
group by name;

proc print; run;

PG

[art297]

I think PGStats already provided what you want, but I would a a quit; statement right after the group by name; statement.