Conditional grouping

deleted_user — Tue, 04 Aug 2009 19:55:22 GMT

Hello,

I currently have data that looks like this.

NAME OFFICE_ID YEAR_QTR END_TERM
BILL 1 20021 20024
BILL 2 20021 20022
JOE 3 20021 20033
JOE 4 20021 20031

For each person, I would like to flag the Office_ID with the latest End_Term, so my data would look something like this

NAME OFFICE_ID YEAR_QTR END_TERM FLAG
BILL 1 20021 20024 1
BILL 2 20021 20022 0
JOE 3 20021 20033 1
JOE 4 20021 20031 0

I would like to achieve this using PROC SQL, but any tips are welcome. Thank you very much. I am using SAS 9.1.

Re: Conditional grouping

ChrisNZ — Tue, 04 Aug 2009 23:08:19 GMT

Generally speaking, SQL is a much poorer query and manipulation language than the sas language. In this case tho, I think you need 2 steps no matter what, so SQL is fine. This should do:

select a.*, a.OFFICE_ID=b.OFFICE_ID as FLAG
from TEST a,
(select NAME, OFFICE_ID
from TEST
group by NAME
having END_TERM=max(END_TERM)) b
where a.NAME=b.NAME;

topic Conditional grouping in SAS Procedures

Conditional grouping

Re: Conditional grouping