topic Re: how do I achieve PIVOT (the one in EXCEL) in SQL? in SAS Programming

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

jimmychoi — Wed, 19 Jun 2019 15:05:08 GMT

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.

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

PaigeMiller — Wed, 19 Jun 2019 15:21:03 GMT

PROC REPORT will do this easily.

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

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

Reeza — Wed, 19 Jun 2019 15:20:28 GMT

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.

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

PGStats — Wed, 19 Jun 2019 20:39:49 GMT

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;

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

hashman — Thu, 20 Jun 2019 00:25:50 GMT

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.

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

PaigeMiller — Thu, 20 Jun 2019 13:13:25 GMT

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.

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

jimmychoi — Fri, 21 Jun 2019 03:18:12 GMT

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.

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

hashman — Fri, 21 Jun 2019 04:11:03 GMT

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.