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).
Can someone suggest a general approach? I've tried a few things, but am getting nowhere.
Thanks.
-- Glenn
@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;
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_: 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;
@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.
@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.
Worked perfectly, thank you!
-- Glenn
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;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: