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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.