topic Re: Run Tests for each year each stock index in SAS Procedures

Run Tests for each year each stock index

rosiecao2509 — Tue, 12 May 2020 21:32:43 GMT

Hello,

I am working on a run test for many different stock indices covering many years. I got the code from SAS help yet it's only run for the whole data, but I need to do it for each year of each index. However, I cannot apply the BY index year in the following step. Please any help would be extremely appreciated.

The following DATA step computes the total number of runs (RUNS), the number of positive values (NUMPOS), and the number of negative values (NUMNEG).

      data runcount;
        set two nobs=nobs;
        if d=0 then delete;
        if d>0 then n+1;
        if d<0 then m+1;
        retain runs 0 numpos 0 numneg 0;
        previous=lag(d);

        if _n_=1 then do;
          runs=1;
          prevpos=.;
          currpos=.;
          prevneg=.;
          currneg=.;
          end;
        else do;
          prevpos=( previous > 0 );
          currpos=( d > 0 );
          prevneg=( previous < 0 );
          currneg=( d < 0 );
      
          if _n_=2 and (currpos and prevpos) then numpos+1;
            else if _n_=2 and (currpos and prevneg) then numneg+1;
            else if _n_=2 and (currneg and prevpos) then numpos+1;
            else if _n_=2 and (currneg and prevneg) then numneg+1;
              
	    if currpos and prevneg then do;
            runs+1;
            numpos+1;
            end;
      
          if currneg and prevpos then do;
            runs+1;
            numneg+1;
            end;
        end;
 
        run;
        data runcount;
          set runcount end=last;
          if last;
        run;

Re: Run Tests for each year each stock index

Reeza — Tue, 12 May 2020 21:52:34 GMT

You need to either add by group processing to your data step or subset it and use a macro on each. I'd probably go towards adding by group processing. If you need help modifying your code you'll need to provide some data that we can work with to test things out.

Re: Run Tests for each year each stock index

rosiecao2509 — Wed, 13 May 2020 11:14:19 GMT

Thanks Reeza for the reply,

I did try to add "by Indices Year" before if function. But it does not seem working. The counting keep going on across all year and all indices. I added a small sample here, if you could help me revising the code. If I do manual I will need to do it 30 years * 63 indices. Really appreciated.

Best

Re: Run Tests for each year each stock index

ballardw — Wed, 13 May 2020 18:40:04 GMT

@rosiecao2509 wrote:

Thanks Reeza for the reply,

I did try to add "by Indices Year" before if function. But it does not seem working. The counting keep going on across all year and all indices. I added a small sample here, if you could help me revising the code. If I do manual I will need to do it 30 years * 63 indices. Really appreciated.

Best

Show the code used to generate the indices. A data set that is incorrect isn't much help.

The typical cause of a count going on is incorrect use of BY variables coupled with First. or Last. combinations (or not used at all). Resetting at the first of group of records is easy:

data want;
   set have;
   by id year;
   if first.id then var=<something>;
run;

So if you wan to count ID

data want;
   set have;
   by id year;
   retain count 0;
   if first.id then count = count+1;
run;

which would set the same value of count for the same value of ID across years. This will require the data to be sorted by ID and Year prior for the By statement to work.

Re: Run Tests for each year each stock index

Reeza — Wed, 13 May 2020 20:49:15 GMT

I'm assuming you modified all the first/if _n_ to account for the new groups and each new group?

Post your modified code in that case.

Re: Run Tests for each year each stock index

rosiecao2509 — Wed, 13 May 2020 20:53:10 GMT

Thanks for your reply.

I would like to count How many positive stock return, how many negative return for each stock indices during each year. The below code does count the number of possitive/negative returns, but for all indices and all years. I want to break the counting down for each year each index instead. So the First.ID code does not apply.

data runcount;
        set two nobs=nobs;
        if d=0 then delete;
        if d>0 then n+1;
        if d<0 then m+1;
        retain runs 0 numpos 0 numneg 0;
        previous=lag(d);

Re: Run Tests for each year each stock index

rosiecao2509 — Wed, 13 May 2020 20:54:53 GMT

Sorry I am not too sure what does that mean. Can you give a bit more details please?

Re: Run Tests for each year each stock index

Reeza — Wed, 13 May 2020 20:57:41 GMT

The parts of your code currently that reset for the start, will need to be reset for the start of each group.

So you need to adjust each section such as:

 if _n_=1 then do;

This currently does a set of specific code at the beginning of the data step process. But now you need to change it to work for the start of each group most likely. I'm assuming when you added the BY statements you also went through and modified these statements to account for the changes.

My suggestion would be to go through your code and comment which lines are conditional and what conditions are required to determine how you need to change each set of code.

Re: Run Tests for each year each stock index

ballardw — Wed, 13 May 2020 21:43:27 GMT

@rosiecao2509 wrote:

Thanks for your reply.

I would like to count How many positive stock return, how many negative return for each stock indices during each year. The below code does count the number of possitive/negative returns, but for all indices and all years. I want to break the counting down for each year each index instead. So the First.ID code does not apply.
data runcount;
        set two nobs=nobs;
        if d=0 then delete;
        if d>0 then n+1;
        if d<0 then m+1;
        retain runs 0 numpos 0 numneg 0;
        previous=lag(d);

WE do not have your data.

Where you say "for each year each index" is very likely to be what you would use instead of First.ID. You have not posted anything with the actual variable names. So I used a generic ID. We have entirely too many folks on this forum that will say "for year" but the variable is something like "year_of_occurence" or some such. So I am not going to guess what your variable names might actually be. Sort your data by your "index" and "year" variables and follow the pattern. Maybe. No data example makes it very hard to provide anything resembling code that will work with your data.