proc sql-aggregate and order by formatted values

[Ronein]

Hello

What is the way to using proc sql to aggregate values (for example sum of revenue) by formatted values and then sort them also by the formatted values?

So in this example I want that order will be :B,C,A

Data ttt;
input ID  team revenue;
cards;
1 833 20
2 850 40
3 833 30
4 865 10
5 840 30
6 840 50
7 850 20
8 833 30
;
Run;

proc format;
value Fmt
833,865='B'
850='A'
840='C'
;
Run;

proc sql;
create table was wanted 
select  put(team,fmt.) as Team_Cat,
          sum(revenue) as Sum_Revenue
From ttt
group by  Team_Cat
;
Quit;

[Reeza]

Why SQL? Why not use PROC MEANS or a SAS proc that's been optimized for that function and will use formats by default?

[PaigeMiller]

---

@Reeza wrote:
Why SQL? Why not use PROC MEANS or a SAS proc that's been optimized for that function and will use formats by default?

---

If I was going to make a list of Maxims, this would be one of them — use the right tool!

[s_lassen]

The order you are asking for is not the formatted value, it is the order of the first input value in the format statement. 

If you want the other order, you will have to specify that order in some other way, e.g.

proc format;             
value Fmt                
833,865='B'              
850='A'                  
840='C'                  
;                        
invalue order            
  'B'=1                  
  'C'=2                  
  'A'=3                  
  other = 999            
  ;                      
Run;

proc sql;                             
create table wanted as                
select  put(team,fmt.) as Team_Cat,   
          sum(revenue) as Sum_Revenue 
From ttt                              
group by  Team_Cat                    
order by  input(Team_Cat,order.)      
;                                     
Quit;

[Ronein]

Questions-
1-Should I add the word "Calculated" before "Team_Cat" in group by and order by ?

2- Shouldn't I add $ before invalue?

3-Will it be also good to use regular proc format for order instead of invalue?

[ballardw]

"1-Should I add the word "Calculated" before "Team_Cat" in group by and order by ?"

Try it both ways and see the result.

"2- Shouldn't I add $ before invalue?"

If asking about a specific statement include the entire statement suggestion. If you mean here:

invalue order            
  'B'=1                  
  'C'=2                  
  'A'=3                  
  other = 999            
  ;                      
Run;

The answer is emphatically "NO". $invalue would very syntactically incorrect.

If you meant to ask "Should the statement be " invalue $order ?" then the answer is still no. The resulting informat creates numeric values so it would be in numeric order. $order would make a character informat.

"3-Will it be also good to use regular proc format for order instead of invalue?"

Try it and see.  SQL will not honor formatted values unless you 1) explicitly create a variable with that value and 2) order by that created variable and even then you may be surprised depending on how well you created your format and the new variable). Some report procedures will use formatted values for order such as Procs Tabulate and Report. But you really want to look at more complex examples

[Ronein]

Sorry , I wanted to ask if need to add $ before order?
invalue $order
'B'=1
'C'=2
'A'=3
other = 999
;
Run;

[Tom]

---

@Ronein wrote:
Sorry , I wanted to ask if need to add $ before order?
invalue $order
'B'=1
'C'=2
'A'=3
other = 999
;
Run;

---

It depends on what you are trying to do. 

If you convert the informat to a CHARACTER informat then you should also add the quotes around the strings you want it to create.

invalue $order
  'B'='1'
  'C'='2'
  'A'='3'
  other = '999'
;

(SAS will add them for you but including them will reduce confusion for the people reading the code.)

For you limited set of only 4 possible results using a character informat to create a character value will not matter.  But if the list expanded beyond 9 items then you will have a problem.  The string '9' comes after the string '10' when sorted.  But the number 9 comes before the number 10 when sorting.

So if the goal is to use the informat to create a value that can help with sorting then using a numeric informat is going to easier.

[Ksharp]

How about this one ?

Data ttt;
input ID  team revenue;
cards;
1 833 20
2 850 40
3 833 30
4 865 10
5 840 30
6 840 50
7 850 20
8 833 30
;
Run;

proc format;
value Fmt
833,865='   B'   /*Add blanks before it*/
850='  A'
840='C'
;
Run;

proc sql;
create table wanted(drop=dummy)  as 
select    put(team,fmt.) as dummy,put(team,fmt. -l) as Team_Cat,
          sum(revenue) as Sum_Revenue
From ttt
group by  dummy,Team_Cat
;
Quit;