topic Re: How to create a sum variable from the following data in SAS Programming

How to create a sum variable from the following data

Davar — Sun, 19 Mar 2017 14:26:50 GMT

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

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

art297 — Sun, 19 Mar 2017 14:39:10 GMT

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

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

Davar — Sun, 19 Mar 2017 18:08:39 GMT

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

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

art297 — Sun, 19 Mar 2017 18:29:05 GMT

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

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

Davar — Sun, 19 Mar 2017 19:04:14 GMT

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

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

art297 — Sun, 19 Mar 2017 19:09:38 GMT

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

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

Davar — Sun, 19 Mar 2017 19:38:44 GMT

Thank you

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

art297 — Sun, 19 Mar 2017 19:49:37 GMT

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

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

Davar — Thu, 23 Mar 2017 02:02:50 GMT

Hi Thank you for help

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

Davar — Thu, 23 Mar 2017 01:59:57 GMT

never mind about part I added