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
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
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.
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
I think PGStats already provided what you want, but I would a a quit; statement right after the group by name; statement.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.