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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.