Data step: Anyway to combine multiple observations into one?

[AaronRogers]

Hello,

I have an Excel sheet of sports scores with which I'm working, and I am trying to organize it in a particular fashion as part of a data step.

Each observation has a date, two teams, two score totals, and a location variable for the city where the game was played.

Some cities have multiple games played in them. What I need to do is make it so each observation lists each city only once so that I can tally totals for things such as games played and goals at each unique city (or location played).

For example, I may have the following:

Date, Team1, T1Goals, Team2, T2Goals, City

07/12/13 Austria 3 Turkey 1 Vienna

08/15/13 Austria 2 Ukraine 1 Innsbruck

09/09/13 Germany 2 Austria 1 Vienna

And I want to organize this in a data step as:

City, GamesPlayedInCity, GoalsScoredByAustriaInCity, AverageGoalsByAustriaInCity

Vienna 2 4 2

Innsbruck 1 2 2

Can anyone suggest a method? I'm at a loss.

[Astounding]

All the questions you are looking to answer can be analyzed using the current structure to the data.  My suggestion is to leave the data as is, and learn a few SAS analysis techniques.  Pick any question you would like, try to arrive at an answer, and post again at the point where you run into trouble.

Good luck.

[Steelers_In_DC]

Here's a start, I do not understand where you are getting the numbers for the other columns so I stopped here.  Maybe this will help:

data have;

infile cards dsd;

informat date mmddyy10.;

format date mmddyy10.;

input Date Team1$ T1Goals Team2$ T2Goals City$9.;

cards;

07/12/13,Austria,3,Turkey,1,Vienna

08/15/13,Austria,2,Ukraine,1,Innsbruck

09/09/13,Germany,2,Austria,1,Vienna

;

run;

proc sql;

create table want as

select distinct city,count(city) as gamesplayedincity

from have

group by city

order by city desc;

[PGStats]

Quite simple with SQL, assuming you are not interested with Austria only :

data goals;

input Date :mmddyy8. Team1 :$12. T1Goals Team2 :$12. T2Goals City :$12.;

team = Team1;

Goals = T1goals;

output;

Team = Team2;

Goals = T2goals;

output;

keep date city team goals;

datalines;

07/12/13 Austria 3 Turkey 1 Vienna

08/15/13 Austria 2 Ukraine 1 Innsbruck

09/09/13 Germany 2 Austria 1 Vienna

;

proc sql;

create table want as

select city, team, count(goals) as nbGames,

    sum(goals) as totalGoals, mean(goals) as meanGoals

from goals

group by city, team;

select * from want;

quit;

PG

[AaronRogers]

Thanks for the responses, but it needs to be done entirely from within a data step. It's a conversion of one data set that looks like this...

View image: img1

To something similar to this...

View image: img2

I'm guessing it needs to be some combination of FIRST.variable and LAST.variable sorted by Venue but no matter what I try, nothing works.

[Steelers_In_DC]

I'm not able to open the attachments, if you can copy/paste or type something in I'll help further.

[Jim_G · Accepted solution]

data have;

infile cards dsd;

informat date mmddyy10.;

format date mmddyy10.;

input Date Team1$ T1Goals Team2$ T2Goals City$9.;

cards;

07/12/13,Austria,3,Turkey,1,Vienna

08/15/13,Austria,2,Ukraine,1,Innsbruck

09/09/13,Germany,2,Austria,1,Vienna

;

proc print; run;

proc sort;  by city;

data one;   set have;   by city;

retain count totgoals;

count+1;

if team1='Austria' then totgoals+t1goals;

if team2='Austria' then totgoals+t2goals;

if last.city then do;

  avg=totgoals/count;   output;

  count=0;  totgoals=0;

end;

drop team1 t1goals team2 t2goals date;

proc print;  id city count totgoals avg;  run;