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 | |
Do you want all weekdays, or do you have a list of holidays that you would like to skip over?
All weekdays will work. Thanks.
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;
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;
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;
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.