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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.