I am using PROC EXPAND to compute daily returns. Theoretically, If the variable is empty, the return should be empty (or at least 0). However, PROC EXPAND gives value for the return of empty observations. Why is it? and How to fix it?
Here is my code:
proc expand data=data_in out=data_out(drop=time);
by fund_ticker constituent_ticker;
convert total_shares_held=etf_growth/ transformout=(ratio 1 -1);
run;
And here is the result:
I believe PROC EXPAND defaults to interpolation using METHOD=SPLINE, which I believe is projecting the spline backwards accross the leading observations (and other observations) with missing values.
Try METHOD=NONE in either the PROC EXPAND statement or the CONVERT statement.
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!
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.