How to create a sum variable from the following data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

How to create a sum variable from the following data

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

 

 


Accepted Solutions
Solution
‎03-22-2017 09:59 PM
PROC Star
Posts: 7,437

Re: How to create a sum variable from the following data

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

View solution in original post


All Replies
PROC Star
Posts: 7,437

Re: How to create a sum variable from the following data

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

 

Occasional Contributor
Posts: 9

Re: How to create a sum variable from the following data

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
.
.
.

Solution
‎03-22-2017 09:59 PM
PROC Star
Posts: 7,437

Re: How to create a sum variable from the following data

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

Occasional Contributor
Posts: 9

Re: How to create a sum variable from the following data

thanks
question I see you use to select statement would tell me what the second one will do?
PROC Star
Posts: 7,437

Re: How to create a sum variable from the following data

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

 

Occasional Contributor
Posts: 9

Re: How to create a sum variable from the following data

Thank you
PROC Star
Posts: 7,437

Re: How to create a sum variable from the following data

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

 

Occasional Contributor
Posts: 9

Re: How to create a sum variable from the following data

[ Edited ]

 

Hi Thank you for help 

      

 

 

 

 

 

      

 

Occasional Contributor
Posts: 9

Re: How to create a sum variable from the following data

never mind about part I added
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 180 views
  • 0 likes
  • 2 in conversation