topic Re: How to I find SUM of salaries across months in SAS Programming

How to I find SUM of salaries across months

Singh77 — Fri, 19 Mar 2021 05:42:03 GMT

There is a table for employee data that looks like

EMPID month salary

1 jan 10

1 Feb 20

1 mar 25

2 Jan 23

2 Feb 43

2 Mar 12

3 Jan 15

3 Feb 23

3 Mar 22

I want a table that displays only employee id and total salary earned by them across months.

resultant table should look something like

1 55

2 78

3 60.

Please help me as I am very new to SAS programming and still a newbie.

Thanks a lot

Re: How to I find SUM of salaries across months

qoit — Fri, 19 Mar 2021 05:55:27 GMT

A very simple SQL procedure should efficiently fix it:

data have;
	input empid $ month $3. salary;
	datalines;
1 jan 10
1 Feb 20
1 mar 25
2 Jan 23
2 Feb 43
2 Mar 12
3 Jan 15
3 Feb 23
3 Mar 22
;
run;

proc sql;
	create table want as
		select empid, sum(salary) as salary format=dollar10.2
			from have
				group by empid;
quit;

Re: How to I find SUM of salaries across months

japelin — Fri, 19 Mar 2021 06:03:58 GMT

try this.

data have;
input EMPID month $ salary;
datalines;
1 jan 10
1 Feb 20
1 mar 25
2 Jan 23
2 Feb 43
2 Mar 12
3 Jan 15
3 Feb 23
3 Mar 22
;
run;

data want(keep=EMPID sum);
  set have;
  by empid;
  if first.empid then sum=0;
  sum+salary;
  if last.empid;
run;

check

SUM Statement

IF Statement: Subsetting

First. and Last. Variables

Re: How to I find SUM of salaries across months

PeterClemmensen — Fri, 19 Mar 2021 06:07:53 GMT

First question: When you say "display a table", do you want a SAS data set or a report?

Both can be accomplished by Proc Summary.

proc summary data = have nway;
   class empid;
   var salary;
   output out = want(drop = _:) sum =;
run;

Result:

empid salary 
1     55 
2     78 
3     60