Bad in the next 12 months flag

[Adnan_Razaq]

Hi All,

I have the a smilar dataset as below consisting multiple account numbers and reporting months and would like to calculate the bad in the next 12 months flag using the value bad flag.

data have;

input Acc Date $ Bad_Flag;

datalines;

 

1 Jan2018 0

1 Feb2018 0

1 Mar2018 0

1 Apr2018 0

1 May2018 0

1 Jun2018 0

1 Jul2018 0

1 Aug2018 0

1 Sep2018 0

1 Oct2018 0

1 Nov2018 0

1 Dec2018 0

1 Jan2019 0

1 Feb2019 0

1 Mar2019 0

1 Apr2019 1

1 May2019 1

1 Jun2019 1

1 Jul2019 1

1 Aug2019 1

1 Sep2019 1

1 Oct2019 1

1 Nov2019 1

1 Dec2019 1

;

run;

I'm trying to create a new varaiable  which will be set to 1 if there's is a bad flag from 12 months from the reporting month. For the example above the bad in next 12 months flag will be 0 until Apr 2018 and then will be 1 going forward until the bad flag is dropped 12 months ahead since there's a bad flag in Apr 2019.

Thanks in advance.

Adnan

[Tommy1]

I would do: 

data want1;
set have;
date12mn= intnx('month', date, 12);
run;

proc sql;
create table want2
as select a.acc, a.date, a.date12mn, a.bad_flag, b.bad_flag as bad_flag12mn
from want1 a
left join want1 b
on a.acc=b.acc and a.date=b.date12mn;
quit;

[Tommy1]

It might be a.date12mn=b.date I can't remember

[AMSAS]

Hi Adnan_Razaq

You could use the point set statement option to process the dataset (see below)
Be warned Continuous loops can occur when you use the POINT= option.

data want ;
	/* Read the Dataset to determine number of observations (nobs set statement option) */
	set have nobs=numberObs ;
	/* Set up a loop (i) to process all the observations in the dataset */
	do i=1 to numberObs ;
		/* variable to point 12 observations ahead */
		plus12=i+12 ;
		/* Only look ahead when there are 12 more observations in the dataset */
		if plus12<=numberObs then do ;
			/* Read the bad_flag for the 12th observation ahead of the current observation */
			/* rename bad_flag to bad_flag_in12 */
			set have(keep=bad_flag rename=(bad_flag=bad_flag_in12)) point=plus12 ;
		end ;
		/* Read the current observation */
		set have point=i  ;
		/* If current observation bad_flag=1 then force bad_flag_in12=0 */
		if bad_flag=1 then
			bad_flag_in12=0 ;
		output want ;
	end ;
	/* Stop processing - Otherwise this will loop */
	stop ;
run ;