topic Re: LOOK BACK - PEEK AHEAD in SAS Programming

LOOK BACK - PEEK AHEAD

UMAnalyst — Wed, 03 Feb 2016 14:58:09 GMT

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.

Re: LOOK BACK - PEEK AHEAD

Kurt_Bremser — Wed, 03 Feb 2016 15:19:28 GMT

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;

Re: LOOK BACK - PEEK AHEAD

RW9 — Wed, 03 Feb 2016 15:19:33 GMT

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;

Re: LOOK BACK - PEEK AHEAD

data_null__ — Wed, 03 Feb 2016 15:28:15 GMT

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?

Re: LOOK BACK - PEEK AHEAD

FreelanceReinh — Wed, 03 Feb 2016 15:38:15 GMT

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

Re: LOOK BACK - PEEK AHEAD

UMAnalyst — Wed, 03 Feb 2016 15:51:07 GMT

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

Re: LOOK BACK - PEEK AHEAD

RW9 — Wed, 03 Feb 2016 16:13:12 GMT

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;

Re: LOOK BACK - PEEK AHEAD

UMAnalyst — Fri, 05 Feb 2016 16:58:21 GMT

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

Re: LOOK BACK - PEEK AHEAD

Kurt_Bremser — Mon, 15 Feb 2016 07:30:05 GMT

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