BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gopilth
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
4 REPLIES 4
PGStats
Opal | Level 21

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
gopilth
Calcite | Level 5

Thank you

pradeepalankar
Obsidian | Level 7

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
Calcite | Level 5

Thank you

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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