How to create a sum variable from the following data

[Davar]

I have following Data set

Active_Messages    Total_Active_Messages    Age_Date    Count    Week_days    BusDay
QUEUE: PR 057                        3                     13-Mar-17          1                   2               5
QUEUE: PR 057                        3                     12-Mar-17          2                   2               5
QUEUE: PR 057                        3                     11-Mar-17          3                   2               5


I want to get these
Active_Messages    Total_Active_Messages    Age_Date    Count    Week_days    BusDay
QUEUE: PR 057                                     9        13-Mar-17          6                    2              5

[art297]

Here is one way:

data have;
  informat Active_Messages $13.;
  informat Age_Date anydtdte9.;
  input Active_Messages &   Total_Active_Messages    Age_Date    Count    Week_days    BusDay;
  cards;
QUEUE: PR 057                        3                     13-Mar-17          1                   2               5
QUEUE: PR 057                        3                     12-Mar-17          2                   2               5
QUEUE: PR 057                        3                     11-Mar-17          3                   2               5
;

proc sql;
  create table want as
    select distinct Active_Messages,
          sum(Total_Active_Messages) as Total_Active_Messages,
          max(Age_Date) as Age_Date format=date9.,
          sum(Count) as Count,
          max(Week_days) as Week_Days,
          max(BusDay) as BusDay
      from have
        group by Active_Messages
  ;
quit;

Art, CEO, AnalystFinder.com

[Davar]

thanks for your respond
I did not explain what happing with my data
1) I have this data that has weekeday from (monday --- Fri... 2,3,4,5,6,). i did replace sat and sun weekdays (1and 7) with Monday(2) which I do not wanted sat sun in my report
but I want the sum of the count and total activity been shown for Moday. Same for BusDay
for example
Active_Messages    Total_Active_Messages    Age_Date    Count    Week_Day    BusDay
QUEUE:1            3            3/17/2017    3    6        1
QUEUE:1            3            3/16/2017    2    5        2
QUEUE:1            3            3/15/2017    1    4        3
QUEUE:1            3            3/14/2017    4    3                   4
QUEUE:1            3            3/13/2017    1    2        5
QUEUE:1            3            3/12/2017    2    2        5
QUEUE:1            3            3/11/2017    3    2        5
after calculation shoud be  here I don't wanted SUN AND SAT
QUEUE:1            9            3/13/2017    6    2        5
I thought if I use
case
where week_days =2 then sum(count ) as count
                                                       max(Week_days)
                                                        max(BusDay)
.........  some how did not work.. ANY Advice

QUEUE:1            3            3/10/2017    1    6        6
QUEUE:1            3            3/9/2017    2    5        7
QUEUE:1            3            3/8/2017    3    4        8
QUEUE:1            3            3/7/2017    2    3        9
QUEUE:1            3            3/6/2017    0    2        10
QUEUE:1            3            3/5/2017    1    2        10
QUEUE:1            3            3/4/2017    1    2        10
SAME HERE
.
.
.

[art297 · Accepted solution]

Not sure exactly what you want, but WEEKDAY is a function you can use. e.g.:

data have;
  informat Active_Messages $13.;
  informat Age_Date anydtdte9.;
  input Active_Messages &   Total_Active_Messages    Age_Date    Count    Week_days    BusDay;
  cards;
QUEUE:1            3            3/17/2017    3    6        1
QUEUE:1            3            3/16/2017    2    5        2
QUEUE:1            3            3/15/2017    1    4        3
QUEUE:1            3            3/14/2017    4    3        4
QUEUE:1            3            3/13/2017    1    2        5
QUEUE:1            3            3/12/2017    2    2        5
QUEUE:1            3            3/11/2017    3    2        5
;

proc sql;
    select distinct Active_Messages,
          sum(Total_Active_Messages) as Total_Active_Messages,
          max(Age_Date) as Age_Date format=date9.,
          sum(Count) as Count,
          max(Week_days) as Week_Days,
          max(BusDay) as BusDay
      from have
        where weekday(age_date) eq 2
          group by Active_Messages
  ;
    select distinct Active_Messages,
          sum(Total_Active_Messages) as Total_Active_Messages,
          max(Age_Date) as Age_Date format=date9.,
          sum(Count) as Count,
          max(Week_days) as Week_Days,
          max(BusDay) as BusDay
      from have
        where weekday(age_date) in (2:6)
          group by Active_Messages
   ;
  quit;

HTH,

Art, CEO, AnalystFinder.com

[Davar]

thanks
question I see you use to select statement would tell me what the second one will do?

[art297]

Since there is a semi-colon between the two sets of proc sql code, it is running two totally independent querries: one for Mondays, and the other for Weekdays

Art, CEO, AnalystFinder.com

[Davar]

Thank you

[art297]

You're welcome! If you feel that your problem has been solved, please mark one of the posts as the solution.

Art, CEO, AnalystFinder.com

[Davar]

Hi Thank you for help 

[Davar]

never mind about part I added