topic Re: Filling blank variables with data from same dataset in SAS Programming

Filling blank variables with data from same dataset

pandhandj — Thu, 16 Feb 2017 15:39:27 GMT

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

Re: Filling blank variables with data from same dataset

ballardw — Thu, 16 Feb 2017 15:54:02 GMT

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;

Re: Filling blank variables with data from same dataset

DanielSantos — Thu, 16 Feb 2017 16:59:34 GMT

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

Re: Filling blank variables with data from same dataset

mkeintz — Thu, 16 Feb 2017 17:13:42 GMT

Consider:

Data want;
SET have;
P=1;
If cats(address1,address2)='' then set have (keep=address1 address2) point=p;
Run;

Re: Filling blank variables with data from same dataset

pandhandj — Fri, 17 Feb 2017 13:59:15 GMT

thankyou to everyone who took the time to help me with this one. Your efferts are very much appreciated.

thanks,

paul