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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.