Add values based on calculation

[gopilth]

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

[PGStats]

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

[gopilth · Accepted solution]

Thank you

[pradeepalankar]

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;

[gopilth]

Thank you