topic Re: Calculating an increase in a variable in SAS Procedures

Calculating an increase in a variable

lgtea57 — Fri, 18 Mar 2011 20:01:28 GMT

Hello,

I am working with a data set that contains an employee ID, grade, pay, rank, gender, and race for three years, 2007, 2008 and 2009. I need to end up with the number of promotions (an increase in grade) for each year and am guessing I need to calculate when there is an increase in grade during each year. I'm just not sure exactly how to do it. (I sitll have soooo much to learn!)

I feel like an array is the best option, but I am new that subject, so wanted to see what someone else thought?

Thank you in advance!

Re: Calculating an increase in a variable

SPR — Fri, 18 Mar 2011 20:17:57 GMT

Hello Igtea57,

If only increase in grade is possible then the solution is very simple
[pre]
proc SQL;
create table r as select
ID, Year, COUNT(distinct Grade) as ng
from i
group by ID, Year
;quit;
[/pre]
Sincerely,
SPR

Re: Calculating an increase in a variable

lgtea57 — Sun, 20 Mar 2011 18:57:31 GMT

There are other columns that change, pay, grade and rank all change, so I am not sure if this will work.

I need to calculate the number of increases in grade for each year, as well as the number of decreases in grade for each year. Any thoughts?

Re: Calculating an increase in a variable

Peter_C — Sun, 20 Mar 2011 23:27:53 GMT

data one ;
input employeeID grade pay rank gender $ race $ year ;
cards ;
1 2 123 4 m c 2007
1 3 123 4 m c 2008
;
proc sql ;
create table g_delta as
select a.employeeID, a.grade as new_g, b.grade as old_g
from one A
join one B
on a.employeeID = B.employeeID
where a.year=2008
and b.year = 2007
and a.grade ne b.grade
;
quit;

Re: Calculating an increase in a variable

SPR — Mon, 21 Mar 2011 14:46:40 GMT

Hello Igtea57,

It is hard to help you not seeing a sample of your data and not having clear requirements. Anyway, accepting data from the last post I'll try:
[pre]
data i;
input EmployeeID grade pay rank gender $ race $ year ;
cards ;
1 2 123 4 m c 2007
1 3 123 4 m c 2008
1 1 123 4 m c 2009
2 5 123 4 m c 2007
2 3 123 4 m c 2008
2 7 123 4 m c 2009
;
proc sort data=i;
by EmployeeID Year;
run;
data r;
retain LG;
set i;
if FIRST.EmployeeID then do; inc=0; dec=0; LG=0; end;
if LG NE 0 then do;
if grade > LG then inc+1;
if grade < LG then dec+1;
end;
LG=grade;
if LAST.EmployeeID;
by EmployeeID;
drop LG;
run;
[/pre]
Sincerely,
SPR