DATA Step, Macro, Functions and more

Add values based on calculation

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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

weekDay/timeScore
Week 1Day 1, time 180
Week1Day1, time2            80
Week 13Day 12, time 180
Week 13Day 12, time 2            80
Week 13Day 9, time 180
Week 13Day 11, time 180
Week 13Day 11, time 2           80
Week 13Day 10, time 180
Week 13Day 10, time 2            80
Week 17Day 14, time 180
Week 17Day 14, time 2            80
Week 17Day 13, time 180
Week 17Day 13, time 2            80
Week 17Day 15, time 180

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

Re: Add values based on calculation


All Replies
Respected Advisor
Posts: 4,920

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

Re: Add values based on calculation

Posted in reply to pradeepalankar

Thank you

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 247 views
  • 0 likes
  • 3 in conversation