How do I Change the previous values based upon the next values with condition

[ggfggrr]

Dear All, 

I had spent sometime on trying to figure out with the help of the SAS community to do the following;

I have the dataset as below; ('month' is numeric)

data have;
    input month id bonus;
    cards;
201701 101 0
201701 102 0
201702 101 0
201702 102 1
201703 101 0
201703 102 0
201704 101 0
201704 102 0
201705 101 0
201705 102 0
201706 101 0
201706 102 0
201707 101 1
201707 102 0
;
run;

I wish to check if the value of the bonus is 1 from the top order. If it is 1, I wish to make the value for bonus as 1 for the previous 6 months as well. Otherwise in which case, I wish to keep it as 0.

Ideally, I wanted to have the following results;

201701 101 1
201701 102 1
201702 101 1
201702 102 1
201703 101 1
201703 102 0
201704 101 1
201704 102 0
201705 101 1
201705 102 0
201706 101 1
201706 102 0
201707 101 1
201707 102 0

I tried to do the above with the following code with the help of community forums, however, I see it does not and looks very much not convincing. It leads to a long steps likely.

proc sql noprint;
    CREATE TABLE want AS
    SELECT a.*, b.bonus AS bonus1,c.bonus as bonus2,d.bonus as bonus3,e.bonus as bonus4,f.bonus as bonus5, g.bonus as bonus6
    FROM have a
    LEFT JOIN have b
    ON b.ID=a.ID AND b.Month=a.Month+1+88*(mod(a.MONTH,100)>11)
	LEFT JOIN have c
    ON c.ID=a.ID AND c.Month=a.Month+2+88*(mod(a.MONTH,100)>10)
	LEFT JOIN have d
    ON d.ID=a.ID AND d.Month=a.Month+3+88*(mod(a.MONTH,100)>9)
	LEFT JOIN have e
    ON e.ID=a.ID AND e.Month=a.Month+4+88*(mod(a.MONTH,100)>8)
	LEFT JOIN have f
    ON f.ID=a.ID AND f.Month=a.Month+5+88*(mod(a.MONTH,100)>7)
	LEFT JOIN have g
    ON g.ID=a.ID AND g.Month=a.Month+6+88*(mod(a.MONTH,100)>6)
    ORDER BY Month, ID
    ;
quit;

Can anyone please help me doing this efficiently?I use SAS EG 7.13 HF2

Thank you,

[Kurt_Bremser · Accepted solution]

Sort in reverse time order, and carry over with retained variables.

Note that I made your "month" into a SAS date, so that SAS date functions and formats can be used. See Maxim 33.

data have;
input month :yymmn6. id bonus;
format month yymmn6.;
cards;
201701 101 0
201701 102 0
201702 101 0
201702 102 1
201703 101 0
201703 102 0
201704 101 0
201704 102 0
201705 101 0
201705 102 0
201706 101 0
201706 102 0
201707 101 1
201707 102 0
;
run;

proc sort data=have;
by id descending month;
run;

data want;
set have;
by id;
retain _bonus _month;
format _month yymmn6.;
if first.id
then do;
  _bonus = 0;
  _month = .;
end;
if bonus = 1
then do;
  _bonus = 1;
  _month = month;
end;
else do; /* bonus = 0 */
  if _bonus = 1
  then do;
    if intck('month',month,_month) > 6
    then do;
      _bonus = 0;
      _month = .;
    end;
    else bonus = 1;
  end;
end;
drop _bonus _month;
run;

proc sort data=want;
by month id;
run;

[ggfggrr]

Thank you so much for your quick help in solving this.

Also, I am referring to one of your other post Convert Character variable / string YYYYMM in date using which I tried to convert the numeric '201701" for example to date SAS date format 201701 (for YYYYMM). However I receive the empty values (.) in the column HAVE. Anything I do it incorrectly?

Kind regards,

[Kurt_Bremser]

See this:

data test;
old_date = 201701;
new_date = input(put(old_date,6.) !! '01',yymmdd8.);
format new_date yymmn6.;
run;