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) Xij * Yij ,
where Xij represents Wage and Yij represents Hours for JobCodeiYearj.
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.
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.
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.
Cool, thank you Reeza.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.