topic Re: Do until lag&i(var) gt 0 in SAS Programming

Do until lag&i(var) gt 0

brophymj — Thu, 13 Apr 2017 10:28:03 GMT

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

Date	Price change	Period
13/04/2017	1%	3
12/04/2017	2%	2
11/04/2017	1%	1
10/04/2017	-1%

order.

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

RW9 — Thu, 13 Apr 2017 10:33:54 GMT

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

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

brophymj — Thu, 13 Apr 2017 11:03:26 GMT

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;

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

Kurt_Bremser — Thu, 13 Apr 2017 11:13:54 GMT

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

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

brophymj — Thu, 13 Apr 2017 12:11:23 GMT

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 :percent5. 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 :percent5. period change_last_neg :percent5.;
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;

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

Kurt_Bremser — Thu, 13 Apr 2017 12:13:43 GMT

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

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

brophymj — Thu, 13 Apr 2017 12:26:04 GMT

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 :percent5.;
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 ercent5. period change_last_neg ercent5.;
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.

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

Kurt_Bremser — Thu, 13 Apr 2017 12:54:56 GMT

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.

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

brophymj — Thu, 13 Apr 2017 13:04:30 GMT

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?

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

Kurt_Bremser — Thu, 13 Apr 2017 13:06:48 GMT

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