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
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;
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?
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?
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;
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;
@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
@srikanthyadav44 sis this work for you?
Great. Glad it worked 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.