Thien:

If you only have two groups, then the following code will do:

*

proc* *sql*;

create table need as

select *, case when max(sub_function)=min(sub_function) then sub_function

else 'Wes/Gen' end as touched_by

from have

group by id;

HTH baogong

On 11/27/07, Thien <coithienthai2002@yahoo.com.au> wrote: > > HAVE: > ID Received_dt Completed_dt Sub_function > CBBBB516 11/11/2007 17/11/2007 Gen > CBBBB516 11/11/2007 16/11/2007 Gen > CBBBB516 11/11/2007 15/11/2007 Gen > CBBBB518 12/11/2007 17/11/2007 Wes > CBBBB518 12/11/2007 16/11/2007 Wes > CBBBB518 12/11/2007 15/11/2007 Gen > CBBBB518 12/11/2007 14/11/2007 Gen > CBBBB519 14/11/2007 16/11/2007 Wes > CBBBB519 14/11/2007 15/11/2007 Wes > > NEED: > ID Received_dt Completed_dt Sub_function touched_by > CBBBB516 11/11/2007 17/11/2007 Gen Gen > CBBBB516 11/11/2007 16/11/2007 Gen Gen > CBBBB516 11/11/2007 15/11/2007 Gen Gen > CBBBB518 12/11/2007 17/11/2007 Wes Wes/Gen > CBBBB518 12/11/2007 16/11/2007 Wes Wes/Gen > CBBBB518 12/11/2007 15/11/2007 Gen Wes/Gen > CBBBB518 12/11/2007 14/11/2007 Gen Wes/Gen > CBBBB519 14/11/2007 16/11/2007 Wes Wes > CBBBB519 14/11/2007 15/11/2007 Wes Wes > > Bonjour to all SAS-Lers, I have a question which I hope someone can > enlighten me how to code to make this work. I data set where I have a > history of all transactions completed by two groups Gen and Wes. > > Given the above data, I need to create a new column called Touch_by which > goes through all records for each ID, and look at the Sub_function to see > whether it is done by 1 team, or both team to assign the following values, > Gen only, Wes/Gen and Wes only. > > For example for ID CBBBB516 since all 3 records for this ID is done by the > Gen team, so the Touch_by column will have Gen for all 3 records. > > But for ID CBBBB518 since the two earlier records are touched by Gen > already before Wes take over, therefore the Touch_by values for all 4 > records are Wes/Gen > > Similarly the last ID only being looked at by Wes so the Touch_by values > is Wes for both records. > > Hope I made this problem clear enough and thank you in advance to all SAS > experts in SAS-L for your invaluable time and advice helping SAS novices > like me, any solutions are greatly appreciated. > > thien >

-- Baoogng Jiang PH.D Office of Outcome Research & Evaluation University of Louisiana at Monroe