topic Proc SQL Conditional sum Across Row and Column in SAS Procedures

Proc SQL Conditional sum Across Row and Column

sachin01663 — Sat, 25 Feb 2012 14:32:34 GMT

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

Proc SQL Conditional sum Across Row and Column

art297 — Sat, 25 Feb 2012 14:36:09 GMT

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.

Proc SQL Conditional sum Across Row and Column

PGStats — Sat, 25 Feb 2012 16:00:21 GMT

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;

Proc SQL Conditional sum Across Row and Column

art297 — Sat, 25 Feb 2012 19:43:12 GMT

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