topic Add values based on calculation in SAS Programming

Add values based on calculation

gopilth — Tue, 17 Dec 2013 02:00:55 GMT

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

Re: Add values based on calculation

PGStats — Tue, 17 Dec 2013 02:41:41 GMT

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.

Re: Add values based on calculation

pradeepalankar — Tue, 17 Dec 2013 13:57:18 GMT

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;

Re: Add values based on calculation

gopilth — Tue, 17 Dec 2013 14:32:07 GMT

Thank you

Re: Add values based on calculation

gopilth — Tue, 17 Dec 2013 14:32:28 GMT

Thank you