topic Re: Data step: Anyway to combine multiple observations into one? in SAS Programming

Data step: Anyway to combine multiple observations into one?

AaronRogers — Tue, 07 Jul 2015 16:30:50 GMT

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.

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

Astounding — Tue, 07 Jul 2015 17:12:51 GMT

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.

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

Steelers_In_DC — Tue, 07 Jul 2015 17:25:46 GMT

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;

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

PGStats — Tue, 07 Jul 2015 17:40:30 GMT

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;

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

AaronRogers — Thu, 09 Jul 2015 12:49:30 GMT

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.

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

Jim_G — Thu, 09 Jul 2015 13:27:30 GMT

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;

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

Steelers_In_DC — Thu, 09 Jul 2015 14:05:08 GMT

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