Transposing with Multiple Years

[jl1005]

Hi everyone,

So I ran a SQL query to return how many times a specific event occurred. Here's a snippet of what that table looks like:

I'd like to merge this table with another table, but this one needs transposed first.

I'd like to keep the year_id and bat_id as they are, but have the year_id's collapsed into one year. So combine the multiple 1999's in to one single row for 1999, for that batter. Then have the event_cd's be the new column headers and the counts fill each row, if that makes sense. The words columns are just labels for the event_cd's; it'd be nice to have those as column headings, but not neccessary. 

So here's what the final table structure would look like, if possible.

I really appreciate the help,

Justin

[Tom · Accepted solution]

The data in your two photographs don't seem to agree with each other.

You should be able to use the ID statement in PROC TRANSPOSE.

proc transpose data=have out=want prefix=event_ ;
  by year_id bat_id ;
  id event_cd ;
  var count;
run;