Solved
Contributor
Posts: 42

Proc SQL Conditional sum Across Row and Column

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

Accepted Solutions
Solution
‎02-25-2012 11:00 AM
Posts: 5,524

Proc SQL Conditional sum Across Row and Column

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

PG

All Replies
PROC Star
Posts: 8,164

Proc SQL Conditional sum Across Row and Column

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.

Solution
‎02-25-2012 11:00 AM
Posts: 5,524

Proc SQL Conditional sum Across Row and Column

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

PG
PROC Star
Posts: 8,164

Proc SQL Conditional sum Across Row and Column

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

🔒 This topic is solved and locked.