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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.