BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
crawfe
Quartz | Level 8

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 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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....

 


 

View solution in original post

2 REPLIES 2
Reeza
Super User

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....

 


 

crawfe
Quartz | Level 8

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!

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 843 views
  • 2 likes
  • 2 in conversation