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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.