Hi Guys,
I have a dataset which looks a bit like this -
AccountNo | AccounID | FistName | SecondName | Address1 | Address2 |
1 | 1 | David | Smith | 1 Any St | Anytown |
2 | 1 | Sharon | Stevens | 12 Somewhere St | Somewhere |
2 | 2 | Peter | Stevens | ||
2 | 3 | Samuel | Stevens | ||
3 | 1 | Robert | Brown | 44 Elsewhere St | Elsewhere |
3 | 2 | Janice | Black | ||
4 | 1 | Senga | Lafferty | 7 Overthere St | Overthere |
4 | 2 | John | Young |
So for each AccountNo, when AccountID = 1 all the data is present.
How do copy the data for AccountID = 1 into the blank fields for AcountID's > 1?
hope that makes sense,
best regards,
paul
Hi.
Not tested, but if all data is always at AccountID = 1 and there is no duplicates by AccountNo, AccountID then this may work.
proc sql noprint;
create table WANT as
select a.*, b.Address1, b.Address2
from HAVE (drop = Address1 Address2) as a left join
HAVE (keep = AccountNo AccountID Address1 Address2) as b
on a.AccountNo = b.AccountNo and b.AccountID = 1;
quit;
Hope it helps.
Daniel Santos @ www.cgd.pt
RETAIN allows you to keep values from one record to the next. So create temporary variables to hold them when assigned and assign as needed.
data want; set have; length laddress1 laddress2 $ 25; /*<= length should be same as the original address variables*/ retain laddress1 laddress2; if not missing(address1) then laddress1=address1; if not missing(address2) then laddress2=address2; if missing(address1) then address1=laddress1; if missing(address2) then address2=laddress2; drop laddress1 laddress2; run;
Hi.
Not tested, but if all data is always at AccountID = 1 and there is no duplicates by AccountNo, AccountID then this may work.
proc sql noprint;
create table WANT as
select a.*, b.Address1, b.Address2
from HAVE (drop = Address1 Address2) as a left join
HAVE (keep = AccountNo AccountID Address1 Address2) as b
on a.AccountNo = b.AccountNo and b.AccountID = 1;
quit;
Hope it helps.
Daniel Santos @ www.cgd.pt
thankyou to everyone who took the time to help me with this one. Your efferts are very much appreciated.
thanks,
paul
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.