how do I achieve PIVOT (the one in EXCEL) in SQL?

[jimmychoi]

Dear sas masters, I am trying to feel like the dummest person in the world, please help.

My data looks like this:

data battlerecord;
input year $ com_a $ com_b $ result_of_a $;
datalines;
2015 INTEL AMD DEFEAT
2015 INTEL AMD WIN
2015 INTEL SAMSUNG WIN
2016 INTEL AMD DRAW
2016 AMD SAMSUNG LOSE
2016 AMD INTEL LOSE
2017 INTEL QUALCOMM WIN

desired output (first row is the column name): 

sorted by com_a and year:
(com_a) (year) (# of 'WIN's) (#of 'LOSE's) (# of 'DRAW's)
INTEL     2015         2                1               0
INTEL     2016         0                0               1
INTEL     2017         1                0               0
AMD       2016         0                2               0

doesn't matter this is achieved through SAS or SQL.

trust me, I have been trying to use the FIRST and LAST, and GROUP BY and HAVING,

but with my poor techniques I can't achieve what I want. desperately needing your help.

[PaigeMiller]

PROC REPORT will do this easily.

Insisting that SQL produce the solution makes your coding much more difficult.

[Reeza]

Do you need the output as a table or report?
It's possible in many different ways, including SQL.

You should show something that you've tried, SQL or data step and we can help you get there.

[PGStats]

Tabulate and transpose can come in useful:

proc tabulate data=battlerecord format=6.0 out=battleCount;
class year com_a com_b result_of_a;
table com_a*year, result_of_a=""*n="";
run;

/* Write the counts to a dataset */
proc transpose data=battleCount out=battleTable(drop=_name_);
by com_a year;
id result_of_a;
var n;
run;

[hashman · Accepted solution]

Hm ... I wonder why you couldn't do it with SQL; it's pretty straightforward:

data battlerecord ;                                                  
  input year $ com_a $ com_b $ result_of_a $ ;                       
  cards ;                                                            
2015 INTEL AMD DEFEAT                                                
2015 INTEL AMD WIN                                                   
2015 INTEL SAMSUNG WIN                                               
2016 INTEL AMD DRAW                                                  
2016 AMD SAMSUNG LOSE                                                
2016 AMD INTEL LOSE                                                  
2017 INTEL QUALCOMM WIN                                              
run ;                                                                
                                                                     
option validvarname = any ;                                          
                                                                     
proc sql ;                                                           
  create table need as                                               
  select com_a as "(com_a)"n                                         
       , year as  "(year)"n                                          
       , sum (result_of_a in ("WIN")          ) as "(# of 'WIN's)"n  
       , sum (result_of_a in ("DEFEAT","LOSE")) as "(# of 'LOSE's)"n 
       , sum (result_of_a in ("DRAW")         ) as "(# of 'DRAW's)"n 
  from   battlerecord                                                
  group  1, 2                                                        
  order  1 desc, 2                                                   
  ;                                                                  
quit ;                                                               

Kind regards

Paul D.

[PaigeMiller]

Yes, in this simple example, creating a PIVOT table is pretty simple using SQL.

But if the real-life problem needs a final output (pivot) table that has 12 or 20 or 75 or any other large number of columns or an un-counted number of columns, SQL is not as simple as in PROC REPORT.

[jimmychoi]

Thanx works great! i didnt know there was a counting function (sum) in SQL.
Plus, what does group 1,2 and order 1 desc, 2 mean?
I always used group followed by a column name and this is a syntax i'm not familiar with.

[hashman]

Welcome.

SUM is not a "counting function" (there's the COUNT function for this purpose) but it does what it says, i.e. sums.

The reason we're SUMmarizing here is because we're dealing with Boolean expressions, such as:

result_of_a in ("WIN") 

which yield 1 if true and 0 if false.

Group 1, 2 (or group BY 1, 2 - you can omit BY in SAS but not in data base specific SQLs) means: Deem the columns 1 and 2 according to their order in the SELECT clause categorical variables by which the summary function (in this case, SUM) aggregates the data. 

Order 1 desc, 2 means: Sort the result set by column 1 included in the SELECT clause in descending order and, within it, by column 2 ascending.

These are standard SQL shortcuts; the actual column names can be used just as well.

Kind regards

Paul D.