## How to create a sum variable from the following data

Solved
Frequent Contributor
Posts: 75

# How to create a sum variable from the following data

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

Accepted Solutions
Solution
‎07-07-2015 04:32 AM
Posts: 4,173

## Re: How to create a sum variable from the following data

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;

All Replies
Solution
‎07-07-2015 04:32 AM
Posts: 4,173

## Re: How to create a sum variable from the following data

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;

🔒 This topic is solved and locked.