topic Re: variable with if condition on rolling basis in SAS Programming

variable with if condition on rolling basis

srikanthyadav44 — Sun, 29 Nov 2020 14:20:15 GMT

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

Re: variable with if condition on rolling basis

PeterClemmensen — Sun, 29 Nov 2020 08:25:28 GMT

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?

Re: variable with if condition on rolling basis

srikanthyadav44 — Sun, 29 Nov 2020 10:42:38 GMT

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

thanks

Re: variable with if condition on rolling basis

PeterClemmensen — Sun, 29 Nov 2020 11:42:46 GMT

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?

Re: variable with if condition on rolling basis

jarapoch — Sun, 29 Nov 2020 13:07:26 GMT

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;

Re: variable with if condition on rolling basis

hhinohar — Sun, 29 Nov 2020 13:35:49 GMT

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;

Re: variable with if condition on rolling basis

srikanthyadav44 — Sun, 29 Nov 2020 14:24:12 GMT

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.

Re: variable with if condition on rolling basis

srikanthyadav44 — Sun, 29 Nov 2020 14:25:40 GMT

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.

Re: variable with if condition on rolling basis

PeterClemmensen — Sun, 29 Nov 2020 19:03:49 GMT

@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

Re: variable with if condition on rolling basis

PeterClemmensen — Mon, 30 Nov 2020 08:21:16 GMT

@srikanthyadav44 sis this work for you?

Re: variable with if condition on rolling basis

srikanthyadav44 — Mon, 30 Nov 2020 10:07:16 GMT

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

Re: variable with if condition on rolling basis

PeterClemmensen — Mon, 30 Nov 2020 10:14:10 GMT

Great. Glad it worked 🙂

Re: variable with if condition on rolling basis

jarapoch — Mon, 30 Nov 2020 12:15:54 GMT

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