DATA Step, Macro, Functions and more

Do until lag&i(var) gt 0

Accepted Solution Solved
Reply
Super Contributor
Posts: 259
Accepted Solution

Do until lag&i(var) gt 0

I'm trying to determine how many periods before the current period before the price change was negative. 

 

I'm tried this code but it doesn't work:

 

%macro temp;

period = 0;
%let i = 1;
%do %until(lag&i.(price_chg_1) >0);

period = period + &i;

&i = &i+1;

%end;

%mend;

%temp;

 

Here is the error messahe I'm getting;

 

Required operator not found in expression: lag&i.(vol_chg_1) >0
ERROR: The condition in the %DO %UNTIL loop, lag&i.(vol_chg_1) >0,
yielded an invalid or missing value, . The macro will stop
executing.
ERROR: The macro TEMP will stop executing.
ERROR 180-322: Statement is not valid or it is used out of proper

 

I want to produce the Period field below

 

DatePrice changePeriod
13/04/20171%3
12/04/20172%2
11/04/20171%1
10/04/2017-1% 


order.


Accepted Solutions
Solution
‎04-13-2017 09:15 AM
Super User
Posts: 7,831

Re: Do until lag&i(var) gt 0


brophymj wrote:

Thanks Kurt. That works now. If I want to retain the price change data from the last period with a price change, how would I go about doing this. Can i use the lag function with period to get this? So I know the last negative change was 3 periods ago, what was that change? 


Use another RETAINed variable for this.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

Re: Do until lag&i(var) gt 0

?? Am sure your just trying to make things more complicated for yourself:

proc sort data=have;
  by date;
run;
data want;
  set have;
  retain flg;
  if _n_=1 then flg=0;
  if flg=1 then do;
    output;
    flg=2;
  end;
  if price_change < 0 then flg=1;
run;

I.e. set flg to 1 when neg is found, then on the next obs its one so output, then set to not 1 so you only get one row output.  

Also, please post test data in the form of a datastep!

Super Contributor
Posts: 259

Re: Do until lag&i(var) gt 0

Hi RW9

 

I'm not trying to make things complicated. I'm just doing the best I can.

 

I tried the code below

 

data have;
input date price_change;
datalines;
3 1
2 1
1 1
0 -1
run;

proc sort data=have;
by date;
run;

data want;
set have;
retain flg;
if _n_=1 then flg=0;
if flg=1 then do;
output;
flg=2;
end;
if price_change < 0 then flg=1;
run;

 

... but it's not working. Heres a larger dataset that might make it a bit clearer (i'm just using number for the date for simplicity):

 

data have;
input date price_change;
datalines;
20 1
19 1
18 1
17 -1

16 1

15 1

14 -1

13 -1

12 1

11  1

10 1

9 1

8 1

7 1

6 1

5 1

4 -1

3 -1

2 1

1 1;

run;

 

I want the final dataset to look like:

 

Note: period field is the number of days since the last negative price change. I will also have a field for positive price change

 

data have;
input date price_change period;
datalines;
20 1 3
19 1 2
18 1 1
17 -1 0

16 1 2

15 1 1

14 -1 0

13 -1 0

12 1 8

11  1 7

10 1 6

9 1 5

8 1 4

7 1 3

6 1 2

5 1 1

4 -1 0

3 -1 0

;

run;

 

 

Super User
Posts: 7,831

Re: Do until lag&i(var) gt 0

The lag() function is a data step function that works at data step runtime.

The macro %do %until, OTOH, is dealt with when the code for the data step is fetched (before compiling and long before runtime).

Therefore your construct makes no sense.

Solve your problem by retaining period in the data step; set it to zero at every negative change and increment it otherwise.

data have;
input date :ddmmyy10. change :percent5. ;
format date ddmmyy10. change percent5.;
cards;
13/04/2017 1%
12/04/2017 2%
11/04/2017 1%
10/04/2017 -1%
;
run;

proc sort data=have;
by date;
run;

data want;
set have;
retain period;
if change < 0
then period = 0;
else period + 1;
run;

proc print data=want noobs;
run;

Result:

      date    change    period

10/04/2017    ( 1%)        0  
11/04/2017      1%         1  
12/04/2017      2%         2  
13/04/2017      1%         3  



---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 259

Re: Do until lag&i(var) gt 0

[ Edited ]
Posted in reply to KurtBremser

Thanks KurtBrenser

 

 

If my dataset had another column called signal and I want to calculate the number of days since the last price change from the signal how would I alter the code below to accomodate that. Here is the revised dataset:

 

When signal is B, calculate the number of periods since the last price decrease. 

 


data have;
input signal $1. date :ddmmyy10. change Smiley Tongueercent5. period;
format signal $1. date ddmmyy10. change percent5. period;
cards;
B 13/04/2017 1% 3
# 12/04/2017 2% .
# 11/04/2017 1% .
# 10/04/2017 -1% .
# 09/04/2017 -1% .
# 08/04/2017 1% .
# 07/04/2017 2% .
# 06/04/2017 3% .
B 05/04/2017 1% 2
# 04/04/2017 2% .
# 03/04/2017 -1% .
# 02/04/2017 -1% .
# 01/04/2017 2% .
# 31/03/2017 1% .
# 30/03/2017 2% .
# 29/03/2017 3% .
# 28/03/2017 4% .
B 27/03/2017 1% 1
# 26/03/2017 -1% .
# 25/03/2017 -1% .
# 24/03/2017 -2% .
# 23/03/2017 1% .
# 22/03/2017 1% .
# 21/03/2017 -1% .
# 20/03/2017 -1% .
;
run;

 

 I then want to soemone pull the price information from the last period where there was a price decrease so my final dataset would look like. 

 

data have;
input signal $1. date :ddmmyy10. change Smiley Tongueercent5. period change_last_neg Smiley Tongueercent5.;
format signal $1. date ddmmyy10. change percent5. period change_last_neg percent5.;
cards;
B 13/04/2017 1% 3 -1%
# 12/04/2017 2% .
# 11/04/2017 1% .
# 10/04/2017 -1% .
# 09/04/2017 -1% .
# 08/04/2017 1% .
# 07/04/2017 2% .
# 06/04/2017 3% .
B 05/04/2017 1% 2 -1%
# 04/04/2017 2% .
# 03/04/2017 -1% .
# 02/04/2017 -1% .
# 01/04/2017 2% .
# 31/03/2017 1% .
# 30/03/2017 2% .
# 29/03/2017 3% .
# 28/03/2017 4% .
B 27/03/2017 1% 1 -1%
# 26/03/2017 -1% .
# 25/03/2017 -1% .
# 24/03/2017 -2% .
# 23/03/2017 1% .
# 22/03/2017 1% .
# 21/03/2017 -1% .
# 20/03/2017 -1% .
;
run;

 

Super User
Posts: 7,831

Re: Do until lag&i(var) gt 0


brophymj wrote:

Thanks KurtBrenser

 

 

If my dataset had another column called signal and I want to calculate the number of days since the last price change from the signal how would I alter the code below to accomodate that. Here is the revised dataset:

 

When signal is B, calculate the number of periods since the last price decrease. 

 



 


Now that should not be so hard, just a slight extension:

data want;
set have;
retain _period;
if change < 0
then _period = 0;
else _period + 1;
if signal = 'B' then period = _period;
drop _period;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 259

Re: Do until lag&i(var) gt 0

[ Edited ]
Posted in reply to KurtBremser

Thanks but it doesn't produce the dataset that's Im looking for. When I run this code:

 

data have;
input signal $1. date :ddmmyy10. change Smiley Tongueercent5.;
format signal $1. date ddmmyy10. change percent5. ;
cards;
B 13/04/2017 1%
# 12/04/2017 2%
# 11/04/2017 1%
# 10/04/2017 -1
# 09/04/2017 -1%
# 08/04/2017 1%
# 07/04/2017 2%
# 06/04/2017 3%
B 05/04/2017 1%
# 04/04/2017 2%
# 03/04/2017 -1%
# 02/04/2017 -1%
# 01/04/2017 2%
# 31/03/2017 1%
# 30/03/2017 2%
# 29/03/2017 3%
# 28/03/2017 4%
B 27/03/2017 1%
# 26/03/2017 -1%
# 25/03/2017 -1%
# 24/03/2017 -2%
# 23/03/2017 1%
# 22/03/2017 1%
# 21/03/2017 -1%
# 20/03/2017 -1%
;
run;

data want;
set have;
retain period;
if change < 0
then period = 0;
else period + 1;
run;

data want;
set want;
retain _period;
if change < 0
then _period = 0;
else _period + 1;
if signal = 'B' then period = _period;
drop _period;
run;

 

I don't get the data set below

 

data have;
input signal $1. date :ddmmyy10. change Smiley Tongueercent5. period change_last_neg Smiley Tongueercent5.;
format signal $1. date ddmmyy10. change percent5. period change_last_neg percent5.;
cards;
B 13/04/2017 1% 3 -1%
# 12/04/2017 2% .
# 11/04/2017 1% .
# 10/04/2017 -1% .
# 09/04/2017 -1% .
# 08/04/2017 1% .
# 07/04/2017 2% .
# 06/04/2017 3% .
B 05/04/2017 1% 2 -1%
# 04/04/2017 2% .
# 03/04/2017 -1% .
# 02/04/2017 -1% .
# 01/04/2017 2% .
# 31/03/2017 1% .
# 30/03/2017 2% .
# 29/03/2017 3% .
# 28/03/2017 4% .
B 27/03/2017 1% 1 -1%
# 26/03/2017 -1% .
# 25/03/2017 -1% .
# 24/03/2017 -2% .
# 23/03/2017 1% .
# 22/03/2017 1% .
# 21/03/2017 -1% .
# 20/03/2017 -1% .
;
run;

 

Also do you know how to pull the price change from the last period with a negative price change i.e.

 

data have;
input signal $1. date :ddmmyy10. change Smiley Tongueercent5. period change_last_neg Smiley Tongueercent5.;
format signal $1. date ddmmyy10. change percent5. period change_last_neg percent5.;
cards;
B 13/04/2017 1% 3 -1%
# 12/04/2017 2% .
# 11/04/2017 1% .
# 10/04/2017 -1% .
# 09/04/2017 -1% .
# 08/04/2017 1% .
# 07/04/2017 2% .
# 06/04/2017 3% .
B 05/04/2017 1% 2 -1%
# 04/04/2017 2% .
# 03/04/2017 -1% .
# 02/04/2017 -1% .
# 01/04/2017 2% .
# 31/03/2017 1% .
# 30/03/2017 2% .
# 29/03/2017 3% .
# 28/03/2017 4% .
B 27/03/2017 1% 1 -1%
# 26/03/2017 -1% .
# 25/03/2017 -1% .
# 24/03/2017 -2% .
# 23/03/2017 1% .
# 22/03/2017 1% .
# 21/03/2017 -1% .
# 20/03/2017 -1% .
;
run;

Super User
Posts: 7,831

Re: Do until lag&i(var) gt 0

Read my initial answer again. You missed the sort, which is crucial for the logic. Also note that only one of the data steps is needed, either the simple version or the extended version from my second answer.

If you need the original most-recent-first order, add a proc sort at the end that reverses the sort order.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 259

Re: Do until lag&i(var) gt 0

Posted in reply to KurtBremser

Thanks Kurt. That works now. If I want to retain the price change data from the last period with a price change, how would I go about doing this. Can i use the lag function with period to get this? So I know the last negative change was 3 periods ago, what was that change? 

Solution
‎04-13-2017 09:15 AM
Super User
Posts: 7,831

Re: Do until lag&i(var) gt 0


brophymj wrote:

Thanks Kurt. That works now. If I want to retain the price change data from the last period with a price change, how would I go about doing this. Can i use the lag function with period to get this? So I know the last negative change was 3 periods ago, what was that change? 


Use another RETAINed variable for this.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 198 views
  • 3 likes
  • 3 in conversation