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,644

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

View solution in original post


All Replies
PROC Star
Posts: 7,360

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

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

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.

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

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