topic Re: Fill in data between two dates within by group with non-missing values from preceding date in New SAS User

Fill in data between two dates within by group with non-missing values from preceding date

aidant01 — Mon, 11 Mar 2019 20:56:16 GMT

Hi everyone,

I would like assistance with the following. For example, for by group account=X and ticker=A, there is a gap between date 1/1/2015 and 1/5/2015. I would like to add rows for 1/2/2015 through 1/4/2015 by taking the non-date values from row of 1/1/2015. I've attached an excel example for reference. Any help will be appreciated. Thank you.


Have

	Account	Ticker	Date	Outstanding Shares	Price
	X	A	01/01/15	100	$1
	X	A	01/05/15	200	$2
	X	B	01/01/15	300	$3
	X	B	01/05/15	600	$6

Want

	Account	Ticker	Date	Outstanding Shares
	X	A	01/01/15	100	$1
	X	A	01/02/15	100	$1
	X	A	01/03/15	100	$1
	X	A	01/04/15	100	$1
	X	A	01/05/15	200	$2
	X	B	01/01/15	300	$3
	X	B	01/02/15	300	$3
	X	B	01/03/15	300	$3
	X	B	01/04/15	300	$3
	X	B	01/05/15	600	$6

Re: Fill in data between two dates within by group with non-missing values from preceding date

Astounding — Mon, 11 Mar 2019 21:19:27 GMT

Do you want all weekdays, or do you have a list of holidays that you would like to skip over?

Re: Fill in data between two dates within by group with non-missing values from preceding date

novinosrin — Mon, 11 Mar 2019 21:40:28 GMT

Hi @aidant01 This is a cheeky solution deliberately to avoid gymnastics using temp vars to hold values. See if this works or in any event , sas communities will offer you better

data have;	 	 	 	 
input Account $	Ticker $	Date	:mmddyy10. OutstandingShares	Price dollar2.;
format date mmddyy10. Price dollar2.;
cards;
X	A	01/01/15	100	$1
X	A	01/05/15	200	$2
X	B	01/01/15	300	$3
X	B	01/05/15	600	$6
	;
proc sql;
create table temp as
select  Account ,Ticker,min(date) as mindate,max(date) as maxdate
from have
group by  Account ,Ticker;
quit;

data want;
if _n_=1 then do;
if 0 then set have;
 dcl hash H (dataset:'have') ;
  h.definekey  ("Account","Ticker","Date") ;
   h.definedata ("OutstandingShares", "price") ;
   h.definedone () ;
 end;
set temp;
do date=mindate to maxdate;
rc=h.find();
output;
end;
drop rc mindate maxdate;
run;

Re: Fill in data between two dates within by group with non-missing values from preceding date

aidant01 — Mon, 11 Mar 2019 21:40:33 GMT

All weekdays will work. Thanks.

Re: Fill in data between two dates within by group with non-missing values from preceding date

PGStats — Mon, 11 Mar 2019 21:57:29 GMT

Proc expand (part of SAS/ETS) does just this, and other kinds of, interpolation:

proc expand data=have out=want to=day;
by account ticker;
id date;
convert oshares price / method=step observed=beginning;
run;

Re: Fill in data between two dates within by group with non-missing values from preceding date

Ksharp — Tue, 12 Mar 2019 12:29:20 GMT

data have;	 
infile cards expandtabs truncover; 
input Account $	Ticker $	Date	:mmddyy10. OutstandingShares	Price dollar2.;
format date mmddyy10. Price dollar2.;
cards;
X	A	01/01/15	100	$1
X	A	01/05/15	200	$2
X	B	01/01/15	300	$3
X	B	01/05/15	600	$6
;
data want;
 merge have have(keep= Account	Ticker	Date
 rename=( Account=A	Ticker=T Date=D) firstobs=2);
 output;
 if Account = A and Ticker = T then do;
   do Date=Date+1 to D-1 ;
    output;
   end;
 end;
 drop A T D;
 run;