topic Re: Need Help with Vertical processing in SAS in SAS Programming

Need Help with Vertical processing in SAS

bhupeshpanwar — Mon, 31 Aug 2020 21:24:26 GMT

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

Re: Need Help with Vertical processing in SAS

ChrisNZ — Mon, 31 Aug 2020 22:03:46 GMT

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

Re: Need Help with Vertical processing in SAS

mkeintz — Tue, 01 Sep 2020 00:36:20 GMT

You need to do 2 things:

maintain a countdown starting at 4 whenever you detect a "first buy".
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;

Re: Need Help with Vertical processing in SAS

Ksharp — Tue, 01 Sep 2020 13:01:52 GMT

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;

Re: Need Help with Vertical processing in SAS

bhupeshpanwar — Wed, 02 Sep 2020 02:36:10 GMT

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

Re: Need Help with Vertical processing in SAS

ChrisNZ — Wed, 02 Sep 2020 02:47:21 GMT

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

Yes it should.

Re: Need Help with Vertical processing in SAS

jimbarbour — Wed, 02 Sep 2020 03:13:19 GMT

@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

Re: Need Help with Vertical processing in SAS

bhupeshpanwar — Wed, 02 Sep 2020 06:20:01 GMT

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;

Re: Need Help with Vertical processing in SAS

mkeintz — Wed, 02 Sep 2020 15:08:03 GMT

@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?

Re: Need Help with Vertical processing in SAS

bhupeshpanwar — Wed, 02 Sep 2020 20:31:29 GMT

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

Re: Need Help with Vertical processing in SAS

bhupeshpanwar — Thu, 03 Sep 2020 01:31:40 GMT

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.