Splitting a set of positive & increasing values-growth, from the fluctuating values (same variable)

[hasnat]

Hi. 

Can someone help me how to sort this attached sample financial accounting data in SAS 9.4 or SAS Studio (the real data could be few hundred thousands obs). 

Link to the SAS file as i could not attach here: https://1drv.ms/f/s!AhVkgVJPuTkWh03K4IT_COxuKZwP

Let EPSFIQ be the representative of earnings of the firm 1690. I want to see how many times over the sample period the firm 1690 has consecutively reported earnings more than last quarter, and by 'consecutive' i expect the firm should have reported earnings more than last quarter 5 consecutive times for the earnings string to be considered as consecutive. So this period of 5 consecutive increased earnings can be called an earnings string. In other words, the condition for such strings is 5 consecutive increases in EPSFIQ.

I want to separate such earnings strings from the rest of the data. 

(Updated)

Thanks in advance for your time. 

[Mar1ene]

hi Hasnat,

I'm not 100% sure I understand the requirement, but if I am correct, you can use the below and specify the output in the proc report by 'key'... I hope this helps...

proc sort data=SASxyz; by FYYYYQ;
data SASxyz1;
set SASxyz;
counter = _N_;
Key = ceil(counter/5);
run;

proc report data=SASxyz1 out=SASxyz2 (drop=_break_) nowd missing;
columns Key FYYYYQ EPSFIQ;
define Key/group;
define FYYYYQ/display;
define EPSFIQ/display;
run;

Output sample:

Key	FYYYYQ	EPSFIQ
1	1980.099976	0.06
	1980.199951	0.06
	1980.300049	0.06
	1980.400024	0.07
	1981.099976	0.14
2	1981.199951	0.16
	1981.300049	0.21
	1981.400024	0.19
	1982.099976	0.24
	1982.199951	0.24
3	1982.300049	0.26
	1982.400024	0.32
	1983.099976	0.4
	1983.199951	0.4
	1983.300049	0.4

Kind Regards,

Marlene

[hasnat]

Hi. Thanks for your help and feedback. I ran your code and edited a bit but realized that description of the post was indeed not sufficient, I have elaborated further.  

[Mar1ene]

Hi Hasnat,

I give up... LOL!  Guess the pro's should rather assist.  Give my below code a shot...

proc sort data=SASxyz; by FYYYYQ;
data SASxyz1 (drop=prev_EPSFIQ move_perc);
set SASxyz;
by FYYYYQ;
format prev_EPSFIQ 8.2 move_perc comma5.2;
prev_EPSFIQ = lag(EPSFIQ);
if prev_EPSFIQ in (.,0) then prev_EPSFIQ=EPSFIQ;
move_perc = ((EPSFIQ-prev_EPSFIQ)/prev_EPSFIQ)*100;
count + 1;
if move_perc<0 then count = 0;
run;

proc report data=SASxyz1 out=SASxyz2 (drop=_break_) nowd missing;
columns FYYYYQ count n;
define FYYYYQ/display;
define count/across;
where count ne 0;
run;

Go well!!

Marlene

[hasnat]

Hi Marlene!

Yes today my course coordinator spent a couple of hours and came up with desired results, your 'if' statement idea was incorporated. Once i do it myself, i would update here as well as i did not explicitly seek teacher's permission to share the code.