Desktop productivity for business analysts and programmers

how do I group age to 10 year bands

Reply
Occasional Contributor
Posts: 6

how do I group age to 10 year bands

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.

SAS Super FREQ
Posts: 703

Re: how do I group age to 10 year bands

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

PROC Star
Posts: 1,143

Re: how do I group age to 10 year bands

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

 

Occasional Contributor
Posts: 6

Re: how do I group age to 10 year bands

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?

PROC Star
Posts: 1,143

Re: how do I group age to 10 year bands

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

 

PROC Star
Posts: 1,143

Re: how do I group age to 10 year bands

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

Occasional Contributor
Posts: 6

Re: how do I group age to 10 year bands

Will do : )

Occasional Contributor
Posts: 6

Re: how do I group age to 10 year bands

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

Ask a Question
Discussion stats
  • 7 replies
  • 194 views
  • 0 likes
  • 3 in conversation