## LOOK BACK - PEEK AHEAD

Solved
Occasional Contributor
Posts: 14

# LOOK BACK - PEEK AHEAD

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&colon;

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.

Accepted Solutions
Solution
‎02-04-2016 06:47 AM
Super User
Posts: 9,599

## Re: LOOK BACK - PEEK AHEAD

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;```

All Replies
Super User
Posts: 10,209

## Re: LOOK BACK - PEEK AHEAD

``````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;

``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 14

## Re: LOOK BACK - PEEK AHEAD

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

Super User
Posts: 10,209

## Re: LOOK BACK - PEEK AHEAD

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".

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,599

## Re: LOOK BACK - PEEK AHEAD

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;```
Posts: 3,852

## Re: LOOK BACK - PEEK AHEAD

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?
Posts: 1,242

## Re: LOOK BACK - PEEK AHEAD

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

Occasional Contributor
Posts: 14

## Re: LOOK BACK - PEEK AHEAD

Yes, good point. If those other vars i.e., CITY, STATE, and ZIP do not match, then the street-fill should not occur.
Solution
‎02-04-2016 06:47 AM
Super User
Posts: 9,599

## Re: LOOK BACK - PEEK AHEAD

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;```
🔒 This topic is solved and locked.