Hi. This is a somewhat off-beat question but maybe someone can help...
I am creating a small table in EG and I am coming up with more Groups than I expect.
I have X's as Category. I take Y and pull the SUBSTR first two letters (looks ok); then I use the SUMMARY COUNT
and Summary Groups=X, Y(2 letter prefix) in Query Builder.
Instead of the expected three line output table, I get a random number of rows for AA, BB, CC.
I might have multiple group rows of AA, where X and AA all look the same! What could be happening
behind the scenes to make EG think that I have multiple groups? Are there hidden attributes?
(BTW the num. of rows is not the number of AA's).
Thanks!
Input: Expected Grouped Output
X Y X Y Count Y Observed:
Grp1 AAxxdfd Grp1 AA 14 Grp1 AA 7
Grp1 AAfjroeo Grp1 BB 7 Grp1 AA 3
Grp1 AAkfirishf Grp1 CC 2 Grp1 AA 4
etc..... Grp1 BB 5
Grp1 BBdieisjd Grp1 BB 2
Grp1 BBfjruspw etc.
etc....
Grp1 CCkfiriusd
Grp1 CCforjhdhs
etc....
1. Either you have invisible characters of some kind - use COMPRESS() to try and remove them
2. You have a format applied to a group variable and SQL doesn't honour formats when doing summaries. You need to use a summary TASK instead of query builder in that case or apply the format explicitly in Query Builder.
@crawfe wrote:
Hi. This is a somewhat off-beat question but maybe someone can help...
I am creating a small table in EG and I am coming up with more Groups than I expect.
I have X's as Category. I take Y and pull the SUBSTR first two letters (looks ok); then I use the SUMMARY COUNT
and Summary Groups=X, Y(2 letter prefix) in Query Builder.
Instead of the expected three line output table, I get a random number of rows for AA, BB, CC.
I might have multiple group rows of AA, where X and AA all look the same! What could be happening
behind the scenes to make EG think that I have multiple groups? Are there hidden attributes?
(BTW the num. of rows is not the number of AA's).
Thanks!
Input: Expected Grouped Output
X Y X Y Count Y Observed:
Grp1 AAxxdfd Grp1 AA 14 Grp1 AA 7
Grp1 AAfjroeo Grp1 BB 7 Grp1 AA 3
Grp1 AAkfirishf Grp1 CC 2 Grp1 AA 4
etc..... Grp1 BB 5
Grp1 BBdieisjd Grp1 BB 2
Grp1 BBfjruspw etc.
etc....
Grp1 CCkfiriusd
Grp1 CCforjhdhs
etc....
1. Either you have invisible characters of some kind - use COMPRESS() to try and remove them
2. You have a format applied to a group variable and SQL doesn't honour formats when doing summaries. You need to use a summary TASK instead of query builder in that case or apply the format explicitly in Query Builder.
@crawfe wrote:
Hi. This is a somewhat off-beat question but maybe someone can help...
I am creating a small table in EG and I am coming up with more Groups than I expect.
I have X's as Category. I take Y and pull the SUBSTR first two letters (looks ok); then I use the SUMMARY COUNT
and Summary Groups=X, Y(2 letter prefix) in Query Builder.
Instead of the expected three line output table, I get a random number of rows for AA, BB, CC.
I might have multiple group rows of AA, where X and AA all look the same! What could be happening
behind the scenes to make EG think that I have multiple groups? Are there hidden attributes?
(BTW the num. of rows is not the number of AA's).
Thanks!
Input: Expected Grouped Output
X Y X Y Count Y Observed:
Grp1 AAxxdfd Grp1 AA 14 Grp1 AA 7
Grp1 AAfjroeo Grp1 BB 7 Grp1 AA 3
Grp1 AAkfirishf Grp1 CC 2 Grp1 AA 4
etc..... Grp1 BB 5
Grp1 BBdieisjd Grp1 BB 2
Grp1 BBfjruspw etc.
etc....
Grp1 CCkfiriusd
Grp1 CCforjhdhs
etc....
Thanks. This got me on the right track. I used STRIP and it worked.
The Grp1's should have been from a pull-down list (no extra blanks).
Now I have to check the source. GIGO. Thanks!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.