PROC EXPAND wierd output

[daradanye]

Hello,

I have a dataset that I need to calculate the rolling average and sum.  The file is attached.  What I intend to do here is to calculate rolling average/sum of variables from -380 days to -20 days (I also require at least 50 non missing observations). The code is below. However, it seems that there are some very large numbers in the output files, starting from around row 800.  The ouput number is not consistent with my manual calculation. I guess it might be due to the missing observations.  I would like to know what I missed here.  Any help is appreciated.

proc expand data = test2 out = test2;
by permno;
id date;
convert logret = logret_new/transformout=(lag 20 movsum 360 trim 70); 
convert turnover = turnover_new/transformout=(lag 20 movave 360 trim 70); 
convert ret = std /transformout=(lag 20 movstd 360 trim 70); 
run;

[ballardw]

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

This difference is likely to be exaggerated sometimes as Excel deals with date/time values quite a bit differently than SAS does (time is fraction of day, SAS does time and datetime values in count of seconds).

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.

[cyrus · Accepted solution]

By default PROC EXPAND interpolates missing values using a cubic spline before applying the transformations requested in the  TRANSFORMOUT= Option.  See the Conversion Methods section of the documentation.  

Try adding the METHOD=NONE Option to the PROC EXPAND Statement, like this:

proc expand data = test2 out = test2 method=NONE;
by permno;
id date;
convert logret = logret_new/transformout=(lag 20 movsum 360 trim 70); 
convert turnover = turnover_new/transformout=(lag 20 movave 360 trim 70); 
convert ret = std /transformout=(lag 20 movstd 360 trim 70); 
run;