topic Re: Create Growth Rates for 41 Variables...Datastep/Loop? in SAS Programming

Create Growth Rates for 41 Variables...Datastep/Loop?

BCNAV — Mon, 03 Sep 2018 23:06:35 GMT

I have 41 variables I need to calculate a growth rate for. I have them defined in a macro loop but I don't think it can be done in a datastep. I have:

data TAFOASAS.TAM_TWRS_FREQ_ALL_G;
	set EGTASK.TAM_TWRS_FREQ_ALL;
	CYYZ_FREQ_G = CYYZ_FREQ / lag12(CYYZ_FREQ) -1;
	format CYYZ_FREQ_G PERCENT10.2;
	drop cyyz_freq;
run;

The only thing that changes is the airport code (CYYZ in this case). Can a list be passed to create the growth rate in a loop for each airport?

thx.

Re: Create Growth Rates for 41 Variables...Datastep/Loop?

ChrisNZ — Mon, 03 Sep 2018 23:13:42 GMT

Can you provide an example of data containing 2 growth variables and 2 airports?

Re: Create Growth Rates for 41 Variables...Datastep/Loop?

Reeza — Mon, 03 Sep 2018 23:22:48 GMT

You can do it a data step or sql merge with a variable that aligns with the time. A data step merge is better because you can then use arrays.

Data want;

merge have have(firstobs=12 rename = ....rename variables);

by airport etc;

Array declarations and processes go here.

run;

This will align the first and 12 observation.

If your airport codes go across, the simplest solution is to transpose it to a long format and then use BY group processing.

Re: Create Growth Rates for 41 Variables...Datastep/Loop?

Astounding — Tue, 04 Sep 2018 01:01:03 GMT

If you had stored your data differently, this whole thing could be done simply. Very simply. One DATA step, no macro language. However, you would need to use a better structure for the data. Instead of adding a prefix for each airport name, store each airport's data on separate observations. For example, instead of storing a variable named CYYZ_FREQ, store a variable named FREQ. Also store a variable named AIRPORT. So when AIRPORT is CYYZ, the variable FREQ holds a value related to the frequency for CYYZ airport.

Do you have the data available in that form?

The current structure to the data is just a bad idea. It turns an easy problem into a difficult one, not just for this application but for most every application that uses this data.