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.
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.
PROC REPORT will do this easily.
Insisting that SQL produce the solution makes your coding much more difficult.
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;
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.