BookmarkSubscribeRSS Feed
DannyDizzy
Fluorite | Level 6

Hi all, another sorting order problem!

I have the following setup:

Cube -> Infomap -> Web Reaport

I have a dimension level "ID and color" (character format) that has values like:

'2 blue'

'55 green'

'105 red'

I would like to have them in the order above, instead i get the following order when choosing acending sort order in the report:

'105 red'

'2 blue'

'55 green'

Is there any character format that can sort theese kind of values in a numerical way? Or is there any way to do this with a user written format in the cube or some trick in the infomap?

/Daniel

7 REPLIES 7
Barnipaz
Obsidian | Level 7

Very dirty.

Create a new variable with the ritgh order. order by it.

In the wrs not order but with the order given by the infomap. or use tha variable just for the order. If you do for just one varaible could be onest

Another way cam be a format with the righ order and order by format on the infomatp but showing no format at all on the WRS.

You should ask if is possible to order in a 'custom way' and I dont't if is possible.

DannyDizzy
Fluorite | Level 6

Actually I can't mess with the data order in the source readed into the cube (oracle table).

The dimension values are actually the following (as characters):

'1', '10', '11', '2', '20'.... about 900 different values.

Is it possible to convert theese to numerical in the cube?

If not, is it possible to set a fix length and inserting zeroes for the values with less then 3 characters?

If not, I have a field with values 'blabla001', 'blabla010', 'blabla002'... is there a format that only takes the last 3 positions in a character field?

really messy situation I know Smiley Sad

Barnipaz
Obsidian | Level 7

trasform the variable char with the '1' '2' .. '100' in numeric and appaly the formmat zn. (n it,2 the max number og digit, see the format z. on the documentation) so that it's shown as '001' '002' ... '100'

DannyDizzy
Fluorite | Level 6

Is it possible to transform char to num in a cube or infomap for a specific level in a dimension? How and where in that case?

ballardw
Super User

If you can use PROC Sort on your data, the SORTSEQ option with Numeric_collation=ON might help.

DannyDizzy
Fluorite | Level 6

The source data to the cube is in oracle and I can´t sort it there. Where could I use the proc sort funcion for a specific level in a dimension, in the cube or infomap?

Tom
Super User Tom
Super User

You mentioned that you cannot modify the source (or at least not easily) since it is in Oracle tables.

You might try creating a format and then applying the format to the field.  Then you can sort by the formatted value. That is actually the default behavior in PROC REPORT.

data have ;

  input color $quote20. ;

cards;

'2 blue'

'55 green'

'105 red'

run;

data want ;

set have ;

fmtname = 'color';

type = 'C';

start = color ;

label =  catx(' ',put(input(scan(color,1,' '),5.),Z3.),scan(color,2,' '));

run;

proc format cntlin=want ;

run;

proc report data=have ;

  column color ;

  format color $color. ;

run;

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
  • 7 replies
  • 2189 views
  • 0 likes
  • 4 in conversation