DATA Step, Macro, Functions and more

LOOK BACK - PEEK AHEAD

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

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:

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
Super User
Posts: 7,977

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst

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;

View solution in original post


All Replies
Super User
Posts: 7,833

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst
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
Occasional Contributor
Posts: 14

Re: LOOK BACK - PEEK AHEAD

Posted in reply to KurtBremser

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: 7,833

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst

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
Super User
Super User
Posts: 7,977

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst

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;
Respected Advisor
Posts: 3,799

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst
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?
Trusted Advisor
Posts: 1,118

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst

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

Occasional Contributor
Posts: 14

Re: LOOK BACK - PEEK AHEAD

Posted in reply to FreelanceReinhard
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
Super User
Posts: 7,977

Re: LOOK BACK - PEEK AHEAD

Posted in reply to UMAnalyst

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 508 views
  • 1 like
  • 5 in conversation