Date:         Mon, 12 Dec 2005 17:54:11 -0500
Reply-To:     Talbot Michael Katz <topkatz@MSN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Talbot Michael Katz <topkatz@MSN.COM>
Subject:      Re: calcuate compouned return
Comments: To: lisiqi77@YAHOO.COM

Hi, Tracy.

You didn't say exactly what went wrong, and I can see a few possible
problems with your example.  To begin with, the data set you show has
variables date, firm, return, but the data set in your query has variables
date, name, return.  I'm presuming that name and firm are the same thing.
Also, one of your dates appears to have a typo (Wed, Jan 1o, 1990).

Once the variable names and date values are straightened out, the next
thing to look at is the WHERE clause:

where "Wed"<=date<="Tue"

if used literally as is will always come up empty, because the values are
being compared as string values, and "Wed" > "Tue".  If you have the dates
stored as date values you can use the following where clause:

where "03JAN1990"d <= date <= "09JAN1990"d

If you have the dates stored as character strings, I recommend converting
them to numeric dates.  If the date strings are like your example (which
actually looks like SAS WEEKDATE17. Format), the following should convert
them (there is probably an easier way):

daten = input(put(input(scan(date,3,", "),2.),z2.) ||
 scan(date,2,", ") || put(input(scan
(date,4,", "),4.),z4.),date9.) ;


If you have a large date range, and you want to collect the returns for
each Wednesday-through-Tuesday period, you have a harder problem.  I can't
see a way to do that in one SQL step (there's an interesting challenge for
our SQL gurus).  I would use a sort and a data step, as in the following
partially tested code (assuming numeric date values):


proc sort data = temp1 (keep = date firm
 return)
    out  = temp1s ;
 by firm date ;
run ;

* only include full wednesday to tuesday weeks ;
data temp2 ;
 set temp1s ;
  by firm date ;
 keep firm date comp_rt ;
 retain comp_rt 0 ;
 if weekday(date) = 4 then do ;
  comp_rt = 1 ;
 end ;
 else if first.firm then do ;
  comp_rt = 0 ;
 end ;
 comp_rt = comp_rt * (1 + return) ;
 if weekday(date) = 3 and comp_rt ne 0
 then do ;
  comp_rt = comp_rt - 1 ;
  output ;
 end ;
run ;



Hope this helps!

--  TMK  --
"The Macro Klutz"


On Mon, 12 Dec 2005 13:03:39 -0800, lisiqi77@YAHOO.COM wrote:

>Hey,
>
>I have a dataset like the following:
>
>date                    firm   return
>Wed, Jan 3, 1990   A   0.3
>Thu, Jan 4, 1990    A    0.4
>..
>Tue, Jan 9, 1990    A     0.6
>Wed, Jan 1o, 1990 A    0.2
>...
>Wed, Jan 3, 1990   B  0.3
>Thu, Jan 4, 1990    B    0.4
>..
>Tue, Jan 9, 1990    B    0.6
>Wed, Jan 10, 1990 B 0.2
>...
>
>My goal is to calculate compouned return for each firm from Wed to next
>Tue. I used the following:
>
>proc sql;
>     create table temp2
>     as select exp(sum(log(1+return)))- 1 as comp_rt
>     from temp1 (keep=date name return )
>     where "Wed"<=date<="Tue"
>     group by name;
>quit;
>
>But it doesn't work. Is there anything wrong or there are better ways
>to calculate them?
>
>Thanks a lot,
>Tracy