BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AaronRogers
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jim_G
Pyrite | Level 9

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

6 REPLIES 6
Astounding
PROC Star

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
Barite | Level 11

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
Opal | Level 21

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
AaronRogers
Calcite | Level 5

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
Barite | Level 11

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

Jim_G
Pyrite | Level 9

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;   

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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