Summing Weekend values to previous Friday

#1
HTML:
I am finding a gap in continuous data that is throwing a 
wrench in a bivariate VAR. I need a way in SAS to sum the weekend 
data of one variable in order to match a day by day set of 
another (stock price data), How do I do that?


set1
date        var1
20130411     2.3
20120412     3.1
20120413     .
20130414     .
20130415     2.4

set2
date            var2
20130411          1.1
20120412          1.5
20120413          3.0
20130414         -2.0
20130415         -0.5

set1
date       var1       var2      combinedvar2
20130411     2.3    1.1      1.1 (thu)
20120412     3.1     1.5     2.5 =(1.5 + 3.0 -2.0) (friday)
20120413     .       3. 0     . (sat)
20130414     .      -2.0      . (sun)
20130415     2.4    -.05     -.05 (mon)

I can't seem to get find a weekend function that would help me do this. Any ideas?

Thanks
vikingo
 
Last edited:
#2
(SAS solution)

This is how I did it. The goal was to take the weekend values for a variable and lump them together with the previous friday value. In my case I need to match up an exogenous variable (daily values) with stock market data..which is Mon-Fri. It took me some time to find all the different tools but the key was to use a two step solution that included either the MOD or the WEEKDAY function followed by finding LEAD values and merging back.

data dataset3; set dataset2;
weekday = weekday(date); * 1=sunday, 2=monday,...,7=saturday;
run;

data temp1(rename=(var2= var2lead1))
temp2(rename=(var2=var2lead2)); *one tempfile for each lead variable needed;
set dataset3;
keep date var2;
date = lag( date );
if date ^= . then output temp1;
date = lag( date );
if date ^= . then output temp2;
run;

data dataset4;
merge dataset3 temp1 temp2;
by date;
run;

data dataset5; set dataset4;
if weekday = 6 then var3 = var2 + var1lead2 + var2lead2; * add lead values to original value for each friday;
else var3 = var2; * every other day keeps original value
if weekday = 1 then delete; *delete sundays;
if weekday = 7 then delete; *delete saturdays;
run;

data dataset6;
merge dataset5 dataother;
by date;
run;
I originally hoped to accomplish this same solution with the proc expand procedure, but I couldn't get the zeros to stay zeros. anyone know why? or how to solve?

proc expand data=dataset3 out=datasetx;
id date;
convert var1=var2lead1 / transform=( lead 1 );
convert var1=var2lead2 / transform=( lead 2 );
run;
I obviously changed the variables from my actual program so I hope I shifted everything over correctly. I am sure there are more elegant solutions. Does this look about right? It worked for me. Hopefully, SAS will get an internal lead function similar to its lag function and then we can get to this solution without having to do the extra merge.

vikingo

 
Last edited: