topic Re: fill down with the given numbers in the same order in New SAS User

fill down with the given numbers in the same order

abdulla — Sun, 10 Feb 2019 19:34:55 GMT

permno	rtrn	HML	SMB
123	0.01	0.02	1.2
123	0.014	1.4	3.5
123	0.008	2.1	0.08
354	0.023	3.4	1.69
354	-0.25	0.006	2.14
354	0.14	1.21	3.54
765	-0.007
765	-0.0036
765	0.032
984	0.11
984	0.005
984	0.004
712	0.006

Hi,

I want to fill the blank spaces by exactly same order from the above by using SMB and HML numbers. Can anyone help me?

Re: fill down with the given numbers in the same order

PeterClemmensen — Sun, 10 Feb 2019 19:38:27 GMT

Can you show your desired result using your example data?

Re: fill down with the given numbers in the same order

PeterClemmensen — Sun, 10 Feb 2019 19:46:41 GMT

If I interpret your question correct, you can do something like this

data have;
input permno $ rtrn HML SMB;
infile datalines missover;
datalines;
123 0.01 0.02 1.2
123 0.014 1.4 3.5
123 0.008 2.1 0.08
354 0.023 3.4 1.69
354 -0.25 0.006 2.14
354 0.14 1.21 3.54
765 -0.007    
765 -0.0036    
765 0.032    
984 0.11    
984 0.005    
984 0.004    
712 0.006    
;

data want(drop=idx);
   set have;
   array a{6} _temporary_;
   array b{6} _temporary_;
   idx=sum(mod(_N_, 6),1);
   if _N_ le 6 then do;
      a[idx]=HML;
      b[idx]=SMB;
   end;
   else do;
      HML=a[idx];
      SMB=b[idx];
   end;
run;

Re: fill down with the given numbers in the same order

Astounding — Sun, 10 Feb 2019 21:01:02 GMT

A simple way:

data want;

set have

hml_next = lag6(hml);

smb_next = lag6(smb);

if _n_ > 6 then do;

hml = hml_next;

smb = smg_next;

end;

drop hml_next smb_next;

run;

Re: fill down with the given numbers in the same order

novinosrin — Sun, 10 Feb 2019 21:35:32 GMT


data have;
input permno $ rtrn HML SMB;
infile datalines missover;
datalines;
123 0.01 0.02 1.2
123 0.014 1.4 3.5
123 0.008 2.1 0.08
354 0.023 3.4 1.69
354 -0.25 0.006 2.14
354 0.14 1.21 3.54
765 -0.007    
765 -0.0036    
765 0.032    
984 0.11    
984 0.005    
984 0.004    
712 0.006    
;
data want ;
If _n_=1 then do;
   declare hash H (ordered: "A") ;
   h.definekey  ("_n_") ;
   h.definedata ("HML","SMB") ;
   h.definedone () ;
   end;
set have;
if coalesce(HML,SMB)>. then do;n+1; h.add();end;
else do;
k= mod(_n_,n);
k=ifn(k=0,n,k);
h.find(key:k);
end;
drop n k;
run;

Re: fill down with the given numbers in the same order

novinosrin — Sun, 10 Feb 2019 22:08:09 GMT

Hi @abdulla Obviously, it's unlikely we are aware of your experience in SAS but nonetheless the question/solutions can be thought of giving rise to many potential caveat

1. what if the number of non missing is not 6, so lagN would not work and will require another pass

2. what if there are many more vars besides sml,hmb , this will lead to having many more temp arrays

3. In essence that will also require the hash to loop through definedata when _n_ =1 while instantiating the hash object but still straight forward.

4. You could probably do for by groups unlike all for the reason associated permno will make it more meningful

5. If you are into heavy data analysis and not necessarily coding, the same question can be turned to a very complex one--(all the more fun for us)

Re: fill down with the given numbers in the same order

abdulla — Sun, 10 Feb 2019 22:10:23 GMT

Hi @novinosrin
Thank you for your comment. Actually, the code I got from draycut worked nicely in my data set. I actually had more variables and more numbers but I made all the adjustments. I have huge data set which I can't use here.