Need Help with Vertical processing in SAS

[bhupeshpanwar]

Hi,

Can someone please help me with the following dataset processing:

time price action;

1     5     buy

2     2     buy

3     8     

4     10   sell 

5     12   buy

6     5     sell

7     2     buy

8     8     

9     10   sell 

10    12   buy

11     8     

12    10   sell 

13    12   buy

I would like to pickup the first buy signal and then write 'hold' for four observations below it irrespective of the following signals. Desired output should look like this:

time price action;

1     5     buy

2     2     hold

3     8     hold

4     10    hold

5     12    hold

6     5     sell

7     2     buy

8     8     hold

9     10    hold

10    12  hold

11     8    hold

12    10   sell 

13    12   buy

I am having trouble with this kind of vertical processing in SAS. I guess an alternate way would be to transpose the data to wide and work with it. I would love to see if this can be achieved without data transformation using loops?

Thanks a lot,

Bhupesh 

[ChrisNZ · Accepted solution]

Like this?

 data HAVE;
  input TIME PRICE ACTION $; 
  retain BUYOBS; 
  if ^BUYOBS & ACTION='buy' then BUYOBS = _N_ ; 
  if BUYOBS < _N_ < BUYOBS+5 then ACTION='hold';
  if _N_ = BUYOBS+4 then BUYOBS=0;
cards;
1 5 buy
2 2 buy
3 8 c
4 10 sell 
5 12 buy
6 5 sell
7 2 buy
8 8 c
9 10 sell 
10 12 buy
11 8 c
12 10 sell 
13 12 buy
run;

Obs	TIME	PRICE	ACTION
1	1	5	buy
2	2	2	hold
3	3	8	hold
4	4	10	hold
5	5	12	hold
6	6	5	sell
7	7	2	buy
8	8	8	hold
9	9	10	hold
10	10	12	hold
11	11	8	hold
12	12	10	sell
13	13	12	buy

[bhupeshpanwar]

Hi Chris,

This is an excellent solution.
Will this code work in a similar fashion with slight modification?

retain BUYOBS 0;
if BUYOBS = 0 & ACTION='buy' then BUYOBS = _N_ ;
if BUYOBS < _N_ < BUYOBS+5 then ACTION='hold';
if _N_ = BUYOBS+4 then BUYOBS=0;

Thanks,

Bhupesh

[ChrisNZ]

>Will this code work in a similar fashion with slight modification?

Yes it should.

[jimbarbour]

@bhupeshpanwar,

One way to be absolutely sure is to first run Chris' version of the code, then run your version, and then to run a Proc Compare on your results.  If the results are a 100% match you have a pretty good assurance that in fact the new version of the code is working the same as the first version.

If we name the dataset from Chris' version "Have" and the dataset from your version "Have2," a simple Proc Compare would look like this:

PROC	COMPARE	BASE	=	Have
				COMPARE	=	Have2;
RUN;

The results after running look like this:

Observation Summary                                                                                                          
                                                                                                                             
Observation      Base  Compare                                                                                               
                                                                                                                             
First Obs           1        1                                                                                               
Last  Obs          13       13                                                                                               
                                                                                                                             
Number of Observations in Common: 13.                                                                                        
Total Number of Observations Read from WORK.HAVE: 13.                                                                        
Total Number of Observations Read from WORK.HAVE2: 13.                                                                       
                                                                                                                             
Number of Observations with Some Compared Variables Unequal: 0.                                                              
Number of Observations with All Compared Variables Equal: 13.                                                                
                                                                                                                             
NOTE: No unequal values were found. All values compared are exactly equal.  

The key phrase I look for is in the Observation Summary:  "All values compared are exactly equal."

Jim

[mkeintz]

You need to do 2 things:

1. maintain a countdown starting at 4 whenever you detect a "first buy".
2. detect "first buy" as any 'buy' following something other than a buy - the lag function is the thing here.

data have;
  infile datalines truncover;
  input time price action $; 
cards;
1 5 buy
2 2 buy
3 8 
4 10 sell 
5 12 buy
6 5 sell
7 2 buy
8 8 
9 10 sell 
10 12 buy
11 8 
12 10 sell 
13 12 buy
run;

data want (drop=_:);
  set have;
  retain _countdown 0;
  if _countdown>0 then do;
    action='hold';
    _countdown+(-1);
  end;
    if action='buy' and lag(action)^='buy' and not (_countdown>0) then _countdown=4;
run;

edit note: removed the erroneous:

  else if action='buy' and lag(action)^='buy' then _countdown=4;

[bhupeshpanwar]

Hi,

your solution works well even without the lag(action)^='buy' comparison. Why do we need that? Can you please check the code below as it works as intended for me? Thanks for your help.

data want (drop=_:);
set have;
retain _countdown 0;
if _countdown>0 then do;
action='hold';
_countdown+(-1);
end;
if action='buy' and countdown=0 then _countdown=4;
run;

[mkeintz]

@bhupeshpanwar 

You're right - the use of lag is superfluous, and using countdown works fine, and is less busy.

Question:  What do you want to do if you have a sequence of, say, 7 buys. Your (and my) program would convert

           buy, buy, buy, buy, buy, buy, buy

into

           buy, hold,hold,hold,hold,buy, hold

The sixth element is a buy, but it is not the first buy.  Do you really want to treat it as the start of another buy-and-hold series?

[bhupeshpanwar]

Thanks for the confirmation.
Yes, this is the correct output. The sixth element in your case should be the start of a new buy hold series.

Bhupesh

[Ksharp]

data have;
  infile datalines truncover;
  input time price action $; 
cards;
1 5 buy
2 2 buy
3 8 
4 10 sell 
5 12 buy
6 5 sell
7 2 buy
8 8 
9 10 sell 
10 12 buy
11 8 
12 10 sell 
13 12 buy
;
run;

data temp;
 set have;
 if action='buy';
 keep time action;
run;
data temp1;
 set temp;
 retain t;
 if _n_=1 then do;t=time;output;end;
 if time>t+4 then do;t=time;output;end;
 keep t;
run;
data temp2;
 set temp1;
 do time=t+1 to t+4;
  output;
 end;
 keep time;
run;
data want;
 if _n_=1 then do;
  declare hash h(dataset:'temp2');
  h.definekey('time');
  h.definedone();
 end;
set have;
if h.check()=0 then action='hold';
run;

[bhupeshpanwar]

Thank you for all the solutions. All three solutions posted work really well. I will accept Chris as a solution since he was the one to post it first. I leaned a lot from all of you. Thanks.