BookmarkSubscribeRSS Feed
jackthecat
Calcite | Level 5

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.

7 REPLIES 7
BrunoMueller
SAS Super FREQ

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
Onyx | Level 15

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
Calcite | Level 5

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
Onyx | Level 15

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
Onyx | Level 15

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
Calcite | Level 5

Will do : )

jackthecat
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3403 views
  • 0 likes
  • 3 in conversation