DATA Step, Macro, Functions and more

Data step: Anyway to combine multiple observations into one?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Data step: Anyway to combine multiple observations into one?

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.


Accepted Solutions
Solution
‎07-09-2015 09:27 AM
Frequent Contributor
Posts: 83

Re: Data step: Anyway to combine multiple observations into one?

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;   

View solution in original post


All Replies
Super User
Posts: 5,069

Re: Data step: Anyway to combine multiple observations into one?

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.

Valued Guide
Posts: 858

Re: Data step: Anyway to combine multiple observations into one?

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;

Respected Advisor
Posts: 4,640

Re: Data step: Anyway to combine multiple observations into one?

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

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

PG
Occasional Contributor
Posts: 6

Re: Data step: Anyway to combine multiple observations into one?

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.

Valued Guide
Posts: 858

Re: Data step: Anyway to combine multiple observations into one?

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

Solution
‎07-09-2015 09:27 AM
Frequent Contributor
Posts: 83

Re: Data step: Anyway to combine multiple observations into one?

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;   

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 331 views
  • 2 likes
  • 5 in conversation