how do I group age to 10 year bands

[jackthecat]

Hi all, new to the forum

I use the query wizard to query data rather then command line type programming.

I need to extract a result set that groups activity by age, but using the age field (single year) as it is results in too large a result set (long story... I'm grouping on other fields as well, that can't be simplified)). I know there is a way to group ages (ie 70-79) to simplify the outputs but I just can't work out how to do it.

I'm afraid I don't know what SAS EG version it is... it's not on my PC, it is a remote instance that I access via an online method. It is quite a restrictive set up, but it's the only way I can access this data.

Any help gratefully received.

[BrunoMueller]

Hi Try an expression like this:

(int((age - 1) / 10) + 1) * 10

To test out your expression you can use a DATA Step like this:

data want;
  do age = 1 to 100;
    age2 = round(age, 10);
    age3 = (int((age - 1) / 10) + 1) * 10;
    output;
  end;
run;

Bruno

[TomKari]

Yes, the query builder in EG has a feature to do exactly this:

1. In your query, click on "Computed Columns". Select New...

2. Select "Recoded column".

3. Select your variable.

4. In the "Specity a replacement" dialog, select "Add...". Then select "Replace a Range".

5. Enter the lower limit and the upper limit for your first age group, and enter a code to replace them with (e.g. replace 0 to 10 with 1).

6. Keep repeating steps 4 and 5 for all of your age groups. When you're done, select "Next".

7. Give your new column a name, select "Next", and select "Finish".

Now you can group on this new variable.

Tom

[jackthecat]

Thanks Tom, that's exactly what I wanted : )

However, it's going fine as I set it up, but then throwing an error when I try to run the query.

Are there any little quirks like the group names have to have quotes round, or can't be non numeric values, or anything like that?

[TomKari]

When I tried it out, I used numeric group names, no quotes.I suggest you do the same, something like this:

1 - 10 -> 1
11 - 20 -> 2
21 - 30 -> 3
31 - 40 -> 4
41 - 50 -> 5

If you want them to have nice descriptions for reporting, you can assign a format to them.

If that's not it, post the log with the error message so we can take a look.

Tom

[TomKari]

I occurred to me as I was looking at my last post, if your ranges are such that they can be derived from a formula, instead of using "Recoded Column" you could use the advanced expression builder with something like:

NewAgeVar = floor(OldAgeVar / 10)

You should get to know all of the options of the Query Builder...in my opinion, it's the most powerful feature in Enterprise Guide.

Tom

[jackthecat]

Will do : )

[jackthecat]

There it is... I had non numeric characters in the group names. Working perfectly now. Many thanks.