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

Greetings, I am working with some complex phone calls logs from an organization that has multiple locations, and a rollover system that routes calls to a given location to multiple extension lines.

 

There is one particular analysis I would like to do, but my SAS skills are not up to the task (I've spent several hours trying to figure this out, but remain 100% stumped).

 

The following is a greatly simplified example of my problem: two calls are shown (one each to Locations A and B), with three rollovers for the first call, and four for the second. What I need to do is flag the rollover with the longest duration for each call (without deleting shorter rollovers).

 

sheet 2.png

Can someone suggest a general approach? I've tried a few things, but am getting nowhere.

 

Thanks.

 

-- Glenn

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

@kiranv_: It does not have to quite so complicated:

 

proc sql;
 select *,
   case
     when b=max(b) then 'Y'
     else 'N'
   end as long_flag
 from aaa
 group by id;

should do the trick!


@kiranv_ wrote:

I took simplied version of your sample and tried to solve the problem. Hope this helps

 data aaa;
 input id c $ b;
 datalines;
 1 vv 100
 1 kk 90
 2 cc 229
 2 dd 100
 ;
 
 proc sql;
 select a.*,
      case when a.id=c.id 
            and a.b= c.b then 'y'
            else 'n' end as long_flag
  from 
 (select * from aaa) a
 left join
 (select id, max(b) as b
 from aaa
 group by id)c
 on a.id =c.id
 and a.b =c.b;
 quit;

 

 

View solution in original post

7 REPLIES 7
kiranv_
Rhodochrosite | Level 12

I took simplied version of your sample and tried to solve the problem. Hope this helps

 data aaa;
 input id c $ b;
 datalines;
 1 vv 100
 1 kk 90
 2 cc 229
 2 dd 100
 ;
 
 proc sql;
 select a.*,
      case when a.id=c.id 
            and a.b= c.b then 'y'
            else 'n' end as long_flag
  from 
 (select * from aaa) a
 left join
 (select id, max(b) as b
 from aaa
 group by id)c
 on a.id =c.id
 and a.b =c.b;
 quit;
s_lassen
Meteorite | Level 14

@kiranv_: It does not have to quite so complicated:

 

proc sql;
 select *,
   case
     when b=max(b) then 'Y'
     else 'N'
   end as long_flag
 from aaa
 group by id;

should do the trick!


@kiranv_ wrote:

I took simplied version of your sample and tried to solve the problem. Hope this helps

 data aaa;
 input id c $ b;
 datalines;
 1 vv 100
 1 kk 90
 2 cc 229
 2 dd 100
 ;
 
 proc sql;
 select a.*,
      case when a.id=c.id 
            and a.b= c.b then 'y'
            else 'n' end as long_flag
  from 
 (select * from aaa) a
 left join
 (select id, max(b) as b
 from aaa
 group by id)c
 on a.id =c.id
 and a.b =c.b;
 quit;

 

 

kiranv_
Rhodochrosite | Level 12

@s_lassen you are absolutely right. Many times I use various RDBMS in which columns in group by and select are required to be same, else you get an error. It is so that I forget sometimes, that it can be done in much easier way  in Proc SQL.

s_lassen
Meteorite | Level 14

@kiranv_: don't I know the feeling! So many times I have tried to use HAVING in Oracle in ways that would be perfectly valid in SAS, and received some mysterious error message for my efforts.


@kiranv_ wrote:

@s_lassen you are absolutely right. Many times I use various RDBMS in which columns in group by and select are required to be same, else you get an error. It is so that I forget sometimes, that it can be done in much easier way  in Proc SQL.


 

glenn_kuly
Calcite | Level 5

Worked perfectly, thank you!

 

-- Glenn

Reeza
Super User

Your data as shown, doesn't provide a way to uniquely identify a call. 


One solution to this problem would be to sort the data - PROC SORT and then use BY processing. If sorted by call and duration, the duration with the longest can be either the first or last record based on how you sort. There's a chapter on BY group processing in the documentaiton. 

 

Another solution, is to use an SQL query - note you still make two passes of the data here. S/he used a subquery but you could probably do it in one step as well, see this example based on SAS SQL. 

 

proc sql;
create table want as
select name, age, sex, height, weight, max(age) as oldest,
case when age= CALCULATED oldest then 1 else 0 end as flag
from sashelp.class
group by sex;
quit;

proc print data=want;run;
glenn_kuly
Calcite | Level 5
Thank you for the replies, I'll try these suggestions out later today, and follow up about the results.

-- Glenn

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
  • 7 replies
  • 707 views
  • 2 likes
  • 4 in conversation