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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

9 REPLIES 9
art297
Opal | Level 21

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

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
Opal | Level 21

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
Calcite | Level 5
thanks
question I see you use to select statement would tell me what the second one will do?
art297
Opal | Level 21

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

 

art297
Opal | Level 21

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

 

Hi Thank you for help 

      

 

 

 

 

 

      

 

Davar
Calcite | Level 5
never mind about part I added

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
  • 9 replies
  • 769 views
  • 0 likes
  • 2 in conversation