## Add values based on calculation

Solved
Occasional Contributor
Posts: 10

# Add values based on calculation

Hi,

I need help calculating the values as shown here from below table based on time where the calculated value (160 in below example) will be populated in a new column. Can someone please advise how it can be done. Thanks in advance.

Week 1             Day 1, time1 + Day 1, time 2                  (80+80 = 160)

Week 13           Day 9, time 1 + Day 9, time 2                 (80+80 = 160)

Week 13          Day 10, time 1 + Day 10, time 2              (80+80 = 160)

Week 13          Day 11, time 1 + Day 11, time 2             (80+80 = 160)

Week 13          Day 12, time 1 + Day 12, time 2              (80+80 = 160)

Week 17          Day 13, time 1 + Day 13, time 2             (80+80 = 160)

Week 17          Day 14, time 1 + Day 14, time 2            (80+80 = 160)

Week 17          Day 15, time 1 + Day 15, time 2            (80+80 = 160)

this is how data  is in the dataset

 week Day/time Score Week 1 Day 1, time 1 80 Week1 Day1, time2 80 Week 13 Day 12, time 1 80 Week 13 Day 12, time 2 80 Week 13 Day 9, time 1 80 Week 13 Day 11, time 1 80 Week 13 Day 11, time 2 80 Week 13 Day 10, time 1 80 Week 13 Day 10, time 2 80 Week 17 Day 14, time 1 80 Week 17 Day 14, time 2 80 Week 17 Day 13, time 1 80 Week 17 Day 13, time 2 80 Week 17 Day 15, time 1 80

Accepted Solutions
Solution
‎12-17-2013 09:32 AM
Occasional Contributor
Posts: 10

Thank you

All Replies
Posts: 5,541

## Re: Add values based on calculation

From what I understand of your request :

data have;
infile datalines missover;
input @'Week' week @'Day' day :comma. @'time' time value;
datalines;
Week 1 Day 1, time 1 80
Week1 Day1, time2             80
Week 13 Day 12, time 1 80
Week 13 Day 12, time 2             80
Week 13 Day 9, time 1 80
Week 13 Day 11, time 1 80
Week 13 Day 11, time 2            80
Week 13 Day 10, time 1 80
Week 13 Day 10, time 2             80
Week 17 Day 14, time 1 80
Week 17 Day 14, time 2             80
Week 17 Day 13, time 1 80
Week 17 Day 13, time 2             80
Week 17 Day 15, time 1 80
Week 17 Day 15, time 2             80
;

proc sql;
create table want as
select week, day, sum(value) as sumValues
from have
group by week, day
having count(distinct time) = 2;
select * from want;
quit;

Note, Day 9 is not included because time 2 is missing. If you want it anyway, remove the having clause from the query.

PG

PG
Solution
‎12-17-2013 09:32 AM
Occasional Contributor
Posts: 10

## Re: Add values based on calculation

Thank you

Frequent Contributor
Posts: 106

## Re: Add values based on calculation

Here is another approach, you can read input data as demonstrated by PGStats in above reply.

proc sort data=have ;

by week day time;

run;

data want(drop=time value1);

retain value1 0;

set have;

by week day time;

if first.day and first.time then do ;value1=value;delete;end;

if last.day and last.time then value=value1+value;

run;

Occasional Contributor
Posts: 10