Solved: variable with if condition on rolling basis

srikanthyadav44 · Posted 11-29-2020 02:28 AM

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

jarapoch · Posted 11-29-2020 08:07 AM

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;

View solution in original post

PeterClemmensen · Posted 11-29-2020 03:25 AM

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?

The DATA to DATA Step Macro
Blog: SASnrd

srikanthyadav44 · Posted 11-29-2020 05:42 AM

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

thanks

PeterClemmensen · Posted 11-29-2020 06:42 AM

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?

The DATA to DATA Step Macro
Blog: SASnrd

jarapoch · Posted 11-29-2020 08:07 AM

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 · Posted 11-29-2020 09:24 AM

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 · Posted 11-30-2020 07:15 AM

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

srikanthyadav44 · Posted 11-29-2020 09:25 AM

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 · Posted 11-29-2020 08:35 AM

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 · Posted 11-29-2020 02:03 PM

@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

The DATA to DATA Step Macro
Blog: SASnrd