02-10-2017 05:54 AM
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.
02-10-2017 07:49 AM
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;
02-10-2017 08:14 AM
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.
02-10-2017 09:20 AM
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?
02-10-2017 09:47 AM
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.
02-10-2017 09:50 AM
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.