Cumulative Income Calculation

[TJ87]

Dear SAS Community:

I'm analyzing the following scenario and I wonder if you could please offer me some coding suggestions:

I would like to calculate cumulative income for part-time employees who work in different job codes at an organization over time. The data is located in two datasets.  

DatasetA includes PersonID (Unique Identifier), JobCode, StartYear (Year Individual Started at Job Code), Years, and Hours (Hours Worked per Year).

Here's an example for DatasetA:

PersonID     JobCode      StartYear     Years     Hours

1111                09           1950              10         640

1111                03           1960              20         1280

1111                06           1980              15         400 

DatasetB is a reference table and gives the hourly Wage (in dollars) for JobCodes 03, 06, and 09 from 1950-1995 (sorted on Year and JobCode): 

Year    JobCode          Wage

1950    03                    5

1951    03                    5.05

…

1995    03                    15

1950    06                    8

…

1995    06                    30

1950    09                    3

…

1995    09                    9

To calculate cumulative income, we need this formula:

Cumulative Income = Σ(i=1, to n JobCode) Σ(j=1, to m Years) X_ij * Y_ij ,

where X_ij  represents Wage and Y_ij  represents Hours for JobCode_iYear_j.

I have two questions about writing this SAS program:

First, what is a macro I can use to call up the 45 Wages by year?

Second, how can I add the total income within each JobCode given the changing wage by year?

I will use the Proc Means ‘Sum’ option to add income across JobCodes.

Thank you in advance for your suggestions. I’m happy to clarify the scenario if you have any questions.     

[Reeza · Accepted solution]

You don't need a macro.

First expand out dataset A to have an observation for every year that a person worked then merge in the salary.

data part1;

set have;

startyear-1;

do i=1 to years;

year=startyear+i;

output;

end;

run;

proc sql;

create table part2 as

select a.*, b.wage

from part1 as a

left join datasetB as b

on a.year=b.year and a.jobcode=b.jobcode

order by personid, year, jobcode;

quit;

I don't know what you want your cumulative income to look like but that should help you get started. If you need more help you'll have to post what you want your output to look like.

[TJ87]

Cool, thank you Reeza.