Help using Base SAS procedures

Proc SQL Conditional sum Across Row and Column

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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)

NameSalaryother earningsType_of ComplaintsAgg Anti Agg Light
Sachin102anti
Suraj153Light
Neha20anti
Mishu4Light
Sapan175anti
Sachin56Light
Suraj23anti
Suraj15Light
Neha205
Mishu5Light
Sapan172anti
Sachin51Anti

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
Respected Advisor
Posts: 4,920

Proc SQL Conditional sum Across Row and Column

Posted in reply to sachin01663

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

View solution in original post


All Replies
PROC Star
Posts: 7,468

Proc SQL Conditional sum Across Row and Column

Posted in reply to sachin01663

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
Respected Advisor
Posts: 4,920

Proc SQL Conditional sum Across Row and Column

Posted in reply to sachin01663

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: 7,468

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 4445 views
  • 0 likes
  • 3 in conversation