BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
brophymj
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

brophymj
Quartz | Level 8

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;

 

 

Kurt_Bremser
Super User

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  



brophymj
Quartz | Level 8

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;

 

Kurt_Bremser
Super User

@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;
brophymj
Quartz | Level 8

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

Kurt_Bremser
Super User

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.

brophymj
Quartz | Level 8

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? 

Kurt_Bremser
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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