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
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
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
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
.
.
.
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
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
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
Hi Thank you for help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.