topic Re: How do I Calculate growth rate and filter out values which fall below a threshold? in SAS Procedures

How do I Calculate growth rate and filter out values which fall below a threshold?

jtpalpillai — Fri, 27 Nov 2020 04:31:24 GMT

Hi,

I wanted to calculate the growth rate between two quarters and then pull out rows which have a growth rate less than the given threshold ( say, 70%), into a new dataset.

The data is like this:

Name Desig. Level Area Country Q1 Q2 Q3 Q4 Q5

ABU Manager Level3 Global UAE 100 109 111 106 121

SAU Chief Level4 Regional Brazil 98 106 119 145 191

Whichever quarter and line item that violates the threshold must be pulled out and saved in another dataset, like:

Name Desig. Level Area Country Q3 Q4

SAU Chief Level4 Regional Brazil 119 145

Re: How do I Calculate growth rate and filter out values which fall below a threshold?

mkeintz — Fri, 27 Nov 2020 05:54:26 GMT

I think you need to put more thought into the design of your output data set. Every row (observation) in the data set will have the same variables. But not every observation will have the same quarters pass your filtering criterion. While Q3 and Q4 for SAU-Chief-Level4-Regional-Brazil qualify, it may be Q2 and Q5 for another observation, and Q4 only for another.

So what do you really want the data set to look like?

Re: How do I Calculate growth rate and filter out values which fall below a threshold?

jtpalpillai — Fri, 27 Nov 2020 08:10:06 GMT

Thanks a lot for your time!

Sorry if I wasn't clearer earlier; I am new to this whole field of coding.

So basically I want to find the line items ( eg.SAU-Chief-Level4-Regional-Brazil ) which has atleast one instance where there is a growth rate of more than 70% between two quarters. So ideally, if there is a >70% growth btwn Q3 and Q4 of SAU-Chief-Level4-Regional-Brazil , then the output must be something like:

Q3 Q4

SAU-Chief-Level4-Regional-Brazil 119 145

and such line items must be exported (proc export) as an excel output, where any line item with atleast one instance of >70% grwth must be filtered and exported.