The data looks like:
Day1Person Day1Hours Day2Person Day2Hours Day3Person Day3Hours
John 3 Jim 2 Jake 4
Mike 4 Mitt 1 Matt 6
Jim 1 Roy 4 Mitt 3
Frank 2
How can I create a data set that consist of the total hours for each person, so it looks like:
Person Hour
John 3
Jim 3
Jake 4
Mike 4
Mitt 4
Matt 6
Roy 4
Frank 2
First transpose your data from a wide into a long structure, then sum it up.
In case your "have" data is in an external file then you could already read it in a way that you get it into a long structure.
Also: Can you please mark in your other post which answers have been helpful/correct for you and eventually also give people some feedback.
data have(keep=person hours);
infile datalines dlm=' ' dsd truncover;
input Day1Person $ Day1Hours Day2Person $ Day2Hours Day3Person $ Day3Hours;
array arr_person {*} Day1Person Day2Person Day3Person;
array arr_hours {*} Day1Hours Day2Hours Day3Hours;
do _i=1 to dim(arr_person);
person=arr_person[_i};
hours=arr_hours[_i};
output;
end;
datalines;
John 3 Jim 2 Jake 4
Mike 4 Mitt 1 Matt 6
Jim 1 Roy 4 Mitt 3
;
run;
proc sql;
create table want as
select
person as Person,
sum(hours) as Hour
from have
group by person
;
quit;
First transpose your data from a wide into a long structure, then sum it up.
In case your "have" data is in an external file then you could already read it in a way that you get it into a long structure.
Also: Can you please mark in your other post which answers have been helpful/correct for you and eventually also give people some feedback.
data have(keep=person hours);
infile datalines dlm=' ' dsd truncover;
input Day1Person $ Day1Hours Day2Person $ Day2Hours Day3Person $ Day3Hours;
array arr_person {*} Day1Person Day2Person Day3Person;
array arr_hours {*} Day1Hours Day2Hours Day3Hours;
do _i=1 to dim(arr_person);
person=arr_person[_i};
hours=arr_hours[_i};
output;
end;
datalines;
John 3 Jim 2 Jake 4
Mike 4 Mitt 1 Matt 6
Jim 1 Roy 4 Mitt 3
;
run;
proc sql;
create table want as
select
person as Person,
sum(hours) as Hour
from have
group by person
;
quit;
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!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.