variable with if condition on rolling basis

[srikanthyadav44]

deal all, 

I have a panel data of variable X of nearly 5000 companies and 37 quarters.

I have to compute two variables i.e, MSP an NZP on rolling basis for preceding 12 quarters as follows.

1. MSP= number of quarters in which  variable X has no change in its value or it has increased  compared to immediately preceding quarter,   in the preceding 12 quarters 

2. NZP= number of quarters in which variable X has no zero values in the preceding 12 quarters. 

i have the quarterly data in the following format. 

Company_Name	Quarter	X
20 Microns Ltd.	Mar-10	0.29
20 Microns Ltd.	Jun-10	0.29
20 Microns Ltd.	Sep-10	0.29
20 Microns Ltd.	Dec-10	0.42
20 Microns Ltd.	Mar-11	0.29
20 Microns Ltd.	Jun-11	0.29
20 Microns Ltd.	Sep-11	0.29
20 Microns Ltd.	Dec-11	0
20 Microns Ltd.	Mar-12	0
20 Microns Ltd.	Jun-12	0.77
20 Microns Ltd.	Sep-12	0.73
20 Microns Ltd.	Dec-12	0
20 Microns Ltd.	Mar-13	3.58
20 Microns Ltd.	Jun-13	1.84
20 Microns Ltd.	Sep-13	4.88
20 Microns Ltd.	Dec-13	0
20 Microns Ltd.	Mar-14	0
20 Microns Ltd.	Jun-14	0
20 Microns Ltd.	Sep-14	0
20 Microns Ltd.	Dec-14	0
20 Microns Ltd.	Mar-15	0.03
20 Microns Ltd.	Jun-15	0
20 Microns Ltd.	Sep-15	0
20 Microns Ltd.	Dec-15	0
20 Microns Ltd.	Mar-16	0
20 Microns Ltd.	Jun-16	0
20 Microns Ltd.	Sep-16	0
20 Microns Ltd.	Dec-16	0.11
20 Microns Ltd.	Mar-17	0.03
20 Microns Ltd.	Jun-17	1.32
20 Microns Ltd.	Sep-17	0.08
20 Microns Ltd.	Dec-17	0.1
20 Microns Ltd.	Mar-18	0.37
20 Microns Ltd.	Jun-18	0.09
20 Microns Ltd.	Sep-18	0.33
20 Microns Ltd.	Dec-18	0.26
20 Microns Ltd.	Mar-19	0.11
3I Infotech Ltd.	Mar-10	39.02
3I Infotech Ltd.	Jun-10	41.18
3I Infotech Ltd.	Sep-10	37.33
3I Infotech Ltd.	Dec-10	40.36
3I Infotech Ltd.	Mar-11	39.46
3I Infotech Ltd.	Jun-11	38.8
3I Infotech Ltd.	Sep-11	36.43
3I Infotech Ltd.	Dec-11	22.01
3I Infotech Ltd.	Mar-12	18.04
3I Infotech Ltd.	Jun-12	57.82
3I Infotech Ltd.	Sep-12	54.74
3I Infotech Ltd.	Dec-12	55.02
3I Infotech Ltd.	Mar-13	55.35

i want the output in the following format

Company_Name	Quarter	X	MSP	NZP
20 Microns Ltd.	Mar-10	0.29
20 Microns Ltd.	Jun-10	0.29
20 Microns Ltd.	Sep-10	0.29
20 Microns Ltd.	Dec-10	0.42
20 Microns Ltd.	Mar-11	0.29
20 Microns Ltd.	Jun-11	0.29
20 Microns Ltd.	Sep-11	0.29
20 Microns Ltd.	Dec-11	0
20 Microns Ltd.	Mar-12	0
20 Microns Ltd.	Jun-12	0.77
20 Microns Ltd.	Sep-12	0.73
20 Microns Ltd.	Dec-12	0	6	9
20 Microns Ltd.	Mar-13	3.58	6	9
20 Microns Ltd.	Jun-13	1.84	5	9
20 Microns Ltd.	Sep-13	4.88	6	9
20 Microns Ltd.	Dec-13	0	5	8

please suggest me a suitable SAS code.

thanks in advance 

[PeterClemmensen]

Do all 5000 companies have 37 quarters?

Should the values be missing up until the 12'th obs for each company like in your posted output? Or should the Counts start earlier?

[srikanthyadav44]

1. Yes. all the 5000 companeis have 37 quarters data.
2. yes. the values should be missing up until 12th observation.

thanks

[PeterClemmensen]

Ok. Why is MSP=4 in you example? I see more than 4 observations with an equal/higher value of X than the obs preceding it?

[jarapoch · Accepted solution]

I have the same doubt as draycut. However, if I supposed that MZPis the number of consecutive quarters in which variable X has no change in its value or it has increased compared to immediately preceding quarter in the preceding 12 quarters,  the code could be this one.

data have;
	infile datalines;
	input
		Company_Name $1-17
		Quarter $ 18-24
		X 25-29;
	datalines;
20 Microns Ltd.  Mar-10 0.29
20 Microns Ltd.  Jun-10 0.29
20 Microns Ltd.  Sep-10 0.29
20 Microns Ltd.  Dec-10 0.42
20 Microns Ltd.  Mar-11 0.29
20 Microns Ltd.  Jun-11 0.29
20 Microns Ltd.  Sep-11 0.29
20 Microns Ltd.  Dec-11 0
20 Microns Ltd.  Mar-12 0
20 Microns Ltd.  Jun-12 0.77
20 Microns Ltd.  Sep-12 0.73
20 Microns Ltd.  Dec-12 0
20 Microns Ltd.  Mar-13 3.58
20 Microns Ltd.  Jun-13 1.84
20 Microns Ltd.  Sep-13 4.88
20 Microns Ltd.  Dec-13 0
20 Microns Ltd.  Mar-14 0
20 Microns Ltd.  Jun-14 0
20 Microns Ltd.  Sep-14 0
20 Microns Ltd.  Dec-14 0
20 Microns Ltd.  Mar-15 0.03
20 Microns Ltd.  Jun-15 0
20 Microns Ltd.  Sep-15 0
20 Microns Ltd.  Dec-15 0
20 Microns Ltd.  Mar-16 0
20 Microns Ltd.  Jun-16 0
20 Microns Ltd.  Sep-16 0
20 Microns Ltd.  Dec-16 0.11
20 Microns Ltd.  Mar-17 0.03
20 Microns Ltd.  Jun-17 1.32
20 Microns Ltd.  Sep-17 0.08
20 Microns Ltd.  Dec-17 0.1
20 Microns Ltd.  Mar-18 0.37
20 Microns Ltd.  Jun-18 0.09
20 Microns Ltd.  Sep-18 0.33
20 Microns Ltd.  Dec-18 0.26
20 Microns Ltd.  Mar-19 0.11
3I Infotech Ltd. Mar-10 39.02
3I Infotech Ltd. Jun-10 41.18
3I Infotech Ltd. Sep-10 37.33
3I Infotech Ltd. Dec-10 40.36
3I Infotech Ltd. Mar-11 39.46
3I Infotech Ltd. Jun-11 38.8
3I Infotech Ltd. Sep-11 36.43
3I Infotech Ltd. Dec-11 22.01
3I Infotech Ltd. Mar-12 18.04
3I Infotech Ltd. Jun-12 57.82
3I Infotech Ltd. Sep-12 54.74
3I Infotech Ltd. Dec-12 55.02
3I Infotech Ltd. Mar-13 55.35
;

data have;
	set have;
	_Quarter=input(compress(Quarter, '-'), MONYY5.); *I want to make sure  data is ordered by descending quarter and I can't use original Quarter because it's a character variable;
	format _Quarter MONYY5.;
run;

proc sort data=have;
	by Company_Name _Quarter;
run;

data want;
	set have;
	by Company_Name;
	retain _lagX  _controlMSP;

	if first.Company_Name or _numQ=12 then
		do;
			_numQ=1;
			_controlMSP=0;
			_MSP=0;
			_NZP=0;
			_lagX=X;
		end;
	else _numQ+1;

	if (_lagX le X and _controlMSP=0) then
		_MSP+1;
	else  _controlMSP=1;

	if X ne 0 then _NZP+1;

	if  _numQ = 12 then
		do;
			MSP=_MSP;
			*number of consecutive quarters in which  variable X has no change in its value or it has increased compared to immediately preceding quarter in the preceding 12 quarters;
			NZP=_NZP;
			*number of quarters in which variable X has no zero values in the preceding 12 quarters;
		end;

	if _numQ < 12 then _lagX=X; *retain last X value;

	drop _:;
run;

[srikanthyadav44]

Dear Mr. jarapoch
the SAS code suggested by you working. but i am not getting the values of MSP and NZP on rolling basis.
i am getting the values of for every 12 quarters.
i have changed the output format for your clear understanding.
please suggest me the changes in the code to get the output in the required format.

[jarapoch]

I've seen that draycut replied you. I'm glad that you found the solution.
By the way, I'm a woman. 😉

[srikanthyadav44]

yes I agree with you. i made a mistake in counting manually.
i have rectified the mistake and made the changes in the model output.
please suggest me the SAS code
thanks in advance.

[hhinohar]

Before providing a code, I want to make sure my output is correct.
Does it look like this?

data want;
infile datalines dlm="," missover;
input Company_Name $ Quarter:monyy. X msp nzp;
format quarter monyy.;
datalines;
20 Micro,MAR10,0.29,,
20 Micro,JUN10,0.29,,
20 Micro,SEP10,0.29,,
20 Micro,DEC10,0.42,,
20 Micro,MAR11,0.29,,
20 Micro,JUN11,0.29,,
20 Micro,SEP11,0.29,,
20 Micro,DEC11,0,,
20 Micro,MAR12,0,,
20 Micro,JUN12,0.77,,
20 Micro,SEP12,0.73,,
20 Micro,DEC12,0,4,9
20 Micro,MAR13,3.58,,
20 Micro,JUN13,1.84,,
20 Micro,SEP13,4.88,,
20 Micro,DEC13,0,,
20 Micro,MAR14,0,,
20 Micro,JUN14,0,,
20 Micro,SEP14,0,,
20 Micro,DEC14,0,,
20 Micro,MAR15,0.03,,
20 Micro,JUN15,0,,
20 Micro,SEP15,0,,
20 Micro,DEC15,0,3,4
20 Micro,MAR16,0,,
20 Micro,JUN16,0,,
20 Micro,SEP16,0,,
20 Micro,DEC16,0.11,,
20 Micro,MAR17,0.03,,
20 Micro,JUN17,1.32,,
20 Micro,SEP17,0.08,,
20 Micro,DEC17,0.1,,
20 Micro,MAR18,0.37,,
20 Micro,JUN18,0.09,,
20 Micro,SEP18,0.33,,
20 Micro,DEC18,0.26,6,10
20 Micro,MAR19,0.11,,
3I Infot,MAR10,39.02,,
3I Infot,JUN10,41.18,,
3I Infot,SEP10,37.33,,
3I Infot,DEC10,40.36,,
3I Infot,MAR11,39.46,,
3I Infot,JUN11,38.8,,
3I Infot,SEP11,36.43,,
3I Infot,DEC11,22.01,,
3I Infot,MAR12,18.04,,
3I Infot,JUN12,57.82,,
3I Infot,SEP12,54.74,,
3I Infot,DEC12,55.02,5,12
3I Infot,MAR13,55.35,,
;
run;

[PeterClemmensen]

@srikanthyadav44 there is a bit of guessing involved, since your description for MSP (as I read it) does not match your posted desired output.

As I understand it, you want MSP to be the number of times X stay the same or increase. However in the sequence 0.29 0.29 0.29 0.42 0.29 0.29 0.29 0 0 0.77 0.73 0 (the first in your data), that logic is fulfilled 7 times. Not 6. Therefore, my interpretation is that you do not want to count zero to zero. Is that correct?

This code does what I think you want in a single pass of the data. Let me know if it works for you and don't hesitate to ask 🙂 

data have;
input Company_Name $ 1-16 Quarter $ X;
datalines;
20 Microns Ltd. Mar-10 0.29
20 Microns Ltd. Jun-10 0.29
20 Microns Ltd. Sep-10 0.29
20 Microns Ltd. Dec-10 0.42
20 Microns Ltd. Mar-11 0.29
20 Microns Ltd. Jun-11 0.29
20 Microns Ltd. Sep-11 0.29
20 Microns Ltd. Dec-11 0
20 Microns Ltd. Mar-12 0
20 Microns Ltd. Jun-12 0.77
20 Microns Ltd. Sep-12 0.73
20 Microns Ltd. Dec-12 0
20 Microns Ltd. Mar-13 3.58
20 Microns Ltd. Jun-13 1.84
20 Microns Ltd. Sep-13 4.88
20 Microns Ltd. Dec-13 0
20 Microns Ltd. Mar-14 0
20 Microns Ltd. Jun-14 0
20 Microns Ltd. Sep-14 0
20 Microns Ltd. Dec-14 0
20 Microns Ltd. Mar-15 0.03
20 Microns Ltd. Jun-15 0
20 Microns Ltd. Sep-15 0
20 Microns Ltd. Dec-15 0
20 Microns Ltd. Mar-16 0
20 Microns Ltd. Jun-16 0
20 Microns Ltd. Sep-16 0
20 Microns Ltd. Dec-16 0.11
20 Microns Ltd. Mar-17 0.03
20 Microns Ltd. Jun-17 1.32
20 Microns Ltd. Sep-17 0.08
20 Microns Ltd. Dec-17 0.1
20 Microns Ltd. Mar-18 0.37
20 Microns Ltd. Jun-18 0.09
20 Microns Ltd. Sep-18 0.33
20 Microns Ltd. Dec-18 0.26
20 Microns Ltd. Mar-19 0.11
3I Infotech Ltd. Mar-10 39.02
3I Infotech Ltd. Jun-10 41.18
3I Infotech Ltd. Sep-10 37.33
3I Infotech Ltd. Dec-10 40.36
3I Infotech Ltd. Mar-11 39.46
3I Infotech Ltd. Jun-11 38.8
3I Infotech Ltd. Sep-11 36.43
3I Infotech Ltd. Dec-11 22.01
3I Infotech Ltd. Mar-12 18.04
3I Infotech Ltd. Jun-12 57.82
3I Infotech Ltd. Sep-12 54.74
3I Infotech Ltd. Dec-12 55.02
3I Infotech Ltd. Mar-13 55.35
;

data want (keep = Company_Name Quarter X MSP NZP);
    array lag[0:11] _temporary_;   
    call missing(of lag[*], obs, MSP, NZP);   
    
    do obs=1 by 1 until (last.Company_Name);        
        set have;
        by Company_Name;

        m = mod(obs, 12);
        lag[m] = X; 

        if obs < 12 then do;
            output; 
            continue;
        end;

        call missing(MSP, NZP); 

        do c = 1 by 1 until (c > 10);
           l = mod(m + c, 12);
           u = mod(m + c + 1, 12);
           if lag[l] <= lag[u] & sum(lag[l], lag[u]) > 0 then MSP + 1; 
        end;
        
        NZP = 12;
        do i = 0 to 11;
           if lag[i] = 0 then NZP +- 1;
        end;
 
        output;
    end;

run;

Result:

MSP NZP Company_Name      Quarter  X 
.   .   20 Microns Ltd.   Mar-10   0.29 
.   .   20 Microns Ltd.   Jun-10   0.29 
.   .   20 Microns Ltd.   Sep-10   0.29 
.   .   20 Microns Ltd.   Dec-10   0.42 
.   .   20 Microns Ltd.   Mar-11   0.29 
.   .   20 Microns Ltd.   Jun-11   0.29 
.   .   20 Microns Ltd.   Sep-11   0.29 
.   .   20 Microns Ltd.   Dec-11   0.00 
.   .   20 Microns Ltd.   Mar-12   0.00 
.   .   20 Microns Ltd.   Jun-12   0.77 
.   .   20 Microns Ltd.   Sep-12   0.73 
6   9   20 Microns Ltd.   Dec-12   0.00 
6   9   20 Microns Ltd.   Mar-13   3.58 
5   9   20 Microns Ltd.   Jun-13   1.84 
5   9   20 Microns Ltd.   Sep-13   4.88 
5   8   20 Microns Ltd.   Dec-13   0.00 
4   7   20 Microns Ltd.   Mar-14   0.00 
3   6   20 Microns Ltd.   Jun-14   0.00 
3   5   20 Microns Ltd.   Sep-14   0.00 
3   5   20 Microns Ltd.   Dec-14   0.00 
3   6   20 Microns Ltd.   Mar-15   0.03 
3   5   20 Microns Ltd.   Jun-15   0.00 
3   4   20 Microns Ltd.   Sep-15   0.00 
2   4   20 Microns Ltd.   Dec-15   0.00 
2   3   20 Microns Ltd.   Mar-16   0.00 
1   2   20 Microns Ltd.   Jun-16   0.00 
1   1   20 Microns Ltd.   Sep-16   0.00 
2   2   20 Microns Ltd.   Dec-16   0.11 
2   3   20 Microns Ltd.   Mar-17   0.03 
3   4   20 Microns Ltd.   Jun-17   1.32 
3   5   20 Microns Ltd.   Sep-17   0.08 
3   6   20 Microns Ltd.   Dec-17   0.10 
4   6   20 Microns Ltd.   Mar-18   0.37 
4   7   20 Microns Ltd.   Jun-18   0.09 
5   8   20 Microns Ltd.   Sep-18   0.33 
5   9   20 Microns Ltd.   Dec-18   0.26 
5   10  20 Microns Ltd.   Mar-19   0.11 
.   .   3I Infotech Ltd.  Mar-10   39.02 
.   .   3I Infotech Ltd.  Jun-10   41.18 
.   .   3I Infotech Ltd.  Sep-10   37.33 
.   .   3I Infotech Ltd.  Dec-10   40.36 
.   .   3I Infotech Ltd.  Mar-11   39.46 
.   .   3I Infotech Ltd.  Jun-11   38.80 
.   .   3I Infotech Ltd.  Sep-11   36.43 
.   .   3I Infotech Ltd.  Dec-11   22.01 
.   .   3I Infotech Ltd.  Mar-12   18.04 
.   .   3I Infotech Ltd.  Jun-12   57.82 
.   .   3I Infotech Ltd.  Sep-12   54.74 
4   12  3I Infotech Ltd.  Dec-12   55.02 
4   12  3I Infotech Ltd.  Mar-13   55.35 

[PeterClemmensen]

@srikanthyadav44 sis this work for you?

[srikanthyadav44]

thanks Mr. draycut
SAS code suggested by you is working excellently.
it is meeting my requirement.

[PeterClemmensen]

Great. Glad it worked 🙂