Hi everyone,
I would like to find duplicate data in street, city, and state by group (ID) and replace a value. When found (see in table *,**), I would like to YREND value from the "duplicate" (or following line) to override the YREND value in the original line.
So, for ID A1007, _N_ 1 and 2 should really only be one line because the values in street, city, and state are identical. I want to replace 1937 with 1945 and remove _N_ = 2 for A1007.
For ID A1008, I would like to replace 1963 with 1977
I have found a way to identify the duplicate data in street, city, and state, but I'm struggling with how to replace the YREND value.
Thanks for your help.
data sample;
infile datalines dsd;
input @1 id $5. @7 entry 1. @9 start 4. @14 end 4. @20 street $20. @40 city $10. @52 state $2. ;
datalines;
A1007 1 1932 1937 111 HURON LN WACO TX
A1007 2 1938 1945 111 HURON LN WACO TX
A1007 3 1946 1953 789 HINES PL ANN ARBOR MI
A1007 4 1954 1959 656 ELM ST LALONDE MI
A1007 5 1960 1988 555 WHO DAT AVE ORLANDO FL
A1008 1 1930 1944 323 PLYMOUTH RD SEATTLE WA
A1008 2 1945 1963 HOUSE PORTLAND OR
A1008 3 1964 1977 HOUSE PORTLAND OR
A1008 4 1978 1983 455 JOY RD DETROIT MI
A1008 5 1984 . 656 WISCONSIN AVE LALONDE MI
A1008 6 1995 9999 555 LAKE ST LAPORTE FL
;
run;
dm 'viewtable work._last_' viewtable;
WANT: ID
A1007 1 1932 1945 111 HURON LN WACO TX (YREND FROM DUPLICATE ROW)
A1007 3 1946 1953 789 HINES PL ANN ARBOR MI
A1007 4 1954 1959 656 ELM ST LALONDE MI
A1007 5 1960 1988 555 WHO DAT AVE ORLANDO FL
A1008 1 1930 1944 323 PLYMOUTH RD SEATTLE WA
A1008 2 1945 1977 HOUSE PORTLAND OR (YREND FROM DUPLICATE ROW)
A1008 4 1978 1983 455 JOY RD DETROIT MI
A1008 5 1984 . 656 WISCONSIN AVE LALONDE MI
A1008 6 1995 9999 555 LAKE ST LAPORTE FL
I do not need to reset the var ENTRY ie., it's OK that A1007, 2 no long exists and the count is 1,3,4,5.
Thank you
... View more
