How to create a sum variable from the following data

[NonSleeper]

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

[Patrick · Accepted solution]

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;