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


Hi!

I have a dimension in the group break, the values in the dimensions are 1 year, 2 years ... 14 years. In the group break, you can choose ascending or decending order (not default data set order for some reason...) for theese dimension values. The problem is that dimensionvalues 10 years - 14 years sneaks in between 1 year and 2 years... Is there any way to keep the natrual order 1-14 instead of 1, 10, 11, 12, 13, 14, 2, 3.....

Thanks in advance!

Daniel

1 ACCEPTED SOLUTION

Accepted Solutions
DannyDizzy
Fluorite | Level 6

With anger came the solution! The solution is hidden characters, by pressing ALT+255(numbers on the num-pad) one can create a character that looks like a blank but isn't recognized by WRS or the web browser as a blank. This character comes after numerics in the sorting order. By inserting a hidden character in front of the 2-digit numbers in the dimension source-table (in oracle oracle), the sorting problem was no more!

1.png

View solution in original post

13 REPLIES 13
FredrikE
Rhodochrosite | Level 12

I think the easiest way is to have a numeric value and then apply a format to get "1 year", "2 years", etc. This can be done in information map studio and common format catalog.

DannyDizzy
Fluorite | Level 6

Hi again, I now have a numeric value, but still it seems to sort 10 before 2...

FredrikE
Rhodochrosite | Level 12

And you have applied the format and it is shown in the report?

I just tried grouping by a formatted column and got the sorting correct....

DannyDizzy
Fluorite | Level 6

The dimension in cube studio:

1.png

The details in infomap studio (no option to set any format):

2.png

The group break in wrs (still wrong sorting):

3.png

BR

Daniel

FredrikE
Rhodochrosite | Level 12

I think you need to apply the format in cube studio, i did my testing with a table....

You have best15. as format now, try your user defined instead!

DannyDizzy
Fluorite | Level 6

Done that, still doesn't work..

proc format;

picture arformat

low-high="00 år";

quit;

then I applied the arformat to the dimension directly in the cube, the result, still the same...

1.png

FredrikE
Rhodochrosite | Level 12

ok, final tip.....

Set sort order on the dimension to Ascending unformatted.....

DannyDizzy
Fluorite | Level 6

Nope, doesn't work. Seems like the sorting function in the group break doesn't care about the choosen sorting type in the cube or infomap. Always seems to sort the formatted value :smileyangry:

We will go with the "ugly" soulution and use blanks in front of the values with only one digit. Thanks Fredrik! The help is always apprecheated!

FredrikE
Rhodochrosite | Level 12

not gooood at all....:)

I have no cube to test with, but it looks like you already tried everything.....:)

DannyDizzy
Fluorite | Level 6

Yeh, I've played around quite a bit 😃

The best soulution was to insert a zero for the on digit year values, 01 year, 02 years ... 11 years, 12 years. But it was accepted as a plan-B soulution. The result of the ugly plan was preferred (if it works) =). I tried to insert a blank with the prefix function for picture format, but it didn't give any result when used directly in the cube. We will now try to insert blanks in the oracle dimension table.

DannyDizzy
Fluorite | Level 6

Wow! WRS is really stubborn! The leading blank soulution does not work because WRS has a built in STRIP function. It's not in place to remove this funtion from WRS either because we already have a great deal of reports and cannot (don't have time/money) research the implecation on thoose... really a tumb down to the sorting function in group break. Why can I have a data set order in the report but not in the group break??

This issue remailns unresolved and we will go with the 01,02,03... soulution...

DannyDizzy
Fluorite | Level 6

With anger came the solution! The solution is hidden characters, by pressing ALT+255(numbers on the num-pad) one can create a character that looks like a blank but isn't recognized by WRS or the web browser as a blank. This character comes after numerics in the sorting order. By inserting a hidden character in front of the 2-digit numbers in the dimension source-table (in oracle oracle), the sorting problem was no more!

1.png

FredrikE
Rhodochrosite | Level 12

Great work!

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!

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.

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
  • 13 replies
  • 2079 views
  • 2 likes
  • 2 in conversation