8 Replies Latest reply: Jul 5, 2016 6:25 PM by Vishnu Chakravaram

# Which code is more efficient for binning ages? For the aficionado's........

Hi!

I want to bin age into the agegroup "0-20", "21-30" etc.. For that

1. Would a Nested IF statement be taking more toll on memory OR

2. This:

let Max_Age = 69;

For a = 1 to 20

A:

LOAD \$(a) as AgeNumber, '0-20' as [Age Group]

AutoGenerate 1;

Next

For a = 21 to 30

Concatenate LOAD \$(a) as AgeNumber, '21-30' as [Age Group]

AutoGenerate 1;

Next

For a = 31 to 40

concatenate LOAD \$(a) as AgeNumber, '31-40' as [Age Group]

AutoGenerate 1;

Next

For a = 41 to 50

concatenate LOAD \$(a) as AgeNumber, '41-50' as [Age Group]

AutoGenerate 1;

Next

For a = 51 to 60

concatenate LOAD \$(a) as AgeNumber, '51-60' as [Age Group]

AutoGenerate 1;

Next

For a = 61 to \$(Max_Age)

concatenate LOAD \$(a) as AgeNumber, '60+' as [Age Group]

AutoGenerate 1;

Next

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

Excellent! Forgot about Interval Match until you reminded.

Thanks Onno

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

If not, please make clear what part of this topic you still need help with .

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

Or a bit simpler

test:

For a = 0 to \$(Max_Age)

AutoGenerate 1;

Next

Noconcatenate

test1:

IF(AgeNumber>=0 And AgeNumber<=20,Dual('0-20',1),

IF(AgeNumber>20 And AgeNumber<=30,Dual('21-30',2),

etc...) As AgeGroup

Resident test;

Drop table test;

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

Nhu, I wanted to avoid Nested IF's because they take lot of toll on the memory.

But thanks for the help though.

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

Here you will find various approaches how to solve such a task: Buckets. My order of solutions would be:

- class()

- mapping with applymap: Mapping … and not the geographical kind

- pick(match())

- nested-if

depending on the requirements of the whole application.

- Marcus

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

Marcus,

I don't know how to use the class function for this particular scenario.

Class function w/o IF's - Would be glad if you could provide an example)

Age      Age Group

0          0-20

1          0-20

57        50-60

68         60+

I have used mapping loads and solved this issue.

But I was wondering if the above script takes a lot of toll on the memory.

• ###### Re: Which code is more efficient for binning ages? For the aficionado's........

Here's the simplified code using Interval Match.

Guys thanks for your help. Especially Onno.

A:

Age

0

11

21

20

35

69

96

];

B:

left Join(A)

IntervalMatch(Age)

Age1, Age2

0, 20

21, 30

31, 40

41, 50

51, 60

61, 200

];

C:

LOAD Age, Replace(Age1&'-'&Age2,'61-200','>60' )as [Age Bucket]

Resident A

Order by Age;

DROP Table A;