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.
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;
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;
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
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".
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;
And what if CITY, STATE or ZIP differ between the previous and next observation?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.