LOOK BACK - PEEK AHEAD

[UMAnalyst]

Hello Everyone,

I need to solve how to fill a char var with a string based on the previous and following values as a conditional.

Example data:

ID               STREET                CITY                 STATE        ZIP        YEAR     IX
1001          999 MAIN ST         TUPELO           MS              99999    1992      1
1001                                        TUPELO           MS              99999    1994      1
1001          999 MAIN ST         TUPELO           MS              99999    1996      1

I'd like the STREET for the second row to be "999 MAIN ST" because 92', 94', and 96' IX=1 and Row 1 STREET is the same as Row 2 STREET.

ID               STREET                CITY                 STATE        ZIP        YEAR     IX
1001          999 MAIN ST         TUPELO           MS              99999    1992      1
1001          999 MAIN ST         TUPELO           MS              99999    1994      1
1001          999 MAIN ST         TUPELO           MS              99999    1996      1

As always thank you for your help.

[Kurt_Bremser]

data set_new (drop=lid lstreet nid nstreet);
merge
  set_old
  set_old (firstobs=2 keep=id street rename=(id=nid street=nstreet))
;
lid = lag(id);
lstreet = lag(street);
if street = . and lid = id and nid = id and lstreet = nstreet then street = lstreet;
run;

[UMAnalyst]

Can this merge execute properly without a BY-statement? Also, is it possible to merge on the same input data twice i.e., "set_old"?

Thanks

[Kurt_Bremser]

A MERGE without a BY merges the records in their physical order in the datasets.

And yes, the same dataset can be used more than once in a SET or MERGE statement in a DATA step. Care needs to be taken with the variables by properly KEEPing and RENAMEing them, so you don't get unexpected overwrites.

What I do is I use the LAG() function to get previous values, while reading the same dataset starting at obs=2 gives me the "peek ahead".

[RW9]

Hi,

So get a list of values where street is not null, then merge that back on.  The coalsce takes the first non-missing.

proc sql;
  create table WANT as
  select A.ID,
           coalesce(A.STREET,B.STREET) as STREET,
           A.CITY,
           A.STATE,
           A.ZIP,
           A.YEAR,
           A.IX
  from   HAVE A
  left join (select distinct ID,IX,STREET from HAVE where STREET is not null) B
  on     A.ID=B.ID
  and   A.IX=B.IX;
quit;

[data_null__]

What if missing street is first or last, no previous or no next respectively. What if there are 2 or more missing in a row?

[FreelanceReinh]

And what if CITY, STATE or ZIP differ between the previous and next observation?

[UMAnalyst]

Yes, good point. If those other vars i.e., CITY, STATE, and ZIP do not match, then the street-fill should not occur.

[RW9 · Accepted solution]

Update per updated code:

proc sql;
  create table WANT as
  select A.ID,
           coalesce(A.STREET,B.STREET) as STREET,
           A.CITY,
           A.STATE,
           A.ZIP,
           A.YEAR,
           A.IX
  from   HAVE A
  left join (select distinct ID,IX,CITY,STATE,ZIP,STREET from HAVE where STREET is not null) B
  on     A.ID=B.ID
  and   A.IX=B.IX
  and   A.CITY=B.CITY
  and   A.STATE=B.STATE
  and   A.ZIP=B.ZIP;
quit;