Data organization: reorganize, manipulate, create variables

[AaronRogers]

Hello,

I'm trying to come up with some method to efficiently organize some data to get some particular results. Below is some sample data:

================================================================

DATA WORK.INFO;

INPUT Team1_Name $ 1-9 Team1_Score 11 Team2_Name $ 13-27 Team2_Score 28 Game_Type $ 30-50 Venue $ 52-62;

DATALINES;

Spain     1 Italy          0 Friendly              Madrid

Aruba     2 Guam           2 Friendly              Oranjestad

Namibia   1 Congo          0 Continental Qualifier Windhoek

Spain     2 Bolivia        0 Friendly              Seville

Australia 0 Spain          3 FIFA World Cup Final  Curitiba

France    1 Spain          0 Friendly              Paris

Ethiopia  1 Algeria        2 Continental Qualifier Addis Ababa

Spain     5 Venezuela      0 Friendly              Malaga

Nepal     0 Maldives       1 Continental Qualifier Kathmandu

Spain     2 Serbia         0 Friendly              St. Gall

Spain     4 Korea Republic 1 Friendly              Berne

;

RUN;

DATA WORK.STUFF;

SET WORK.INFO;

/** ALL ORGANZATION GOES HERE AND NO WHERE ELSE. **/

/** SO, PROC SQL IS OFF THE TABLE. **/

RUN;

================================================================

I'm trying to create a new data set (WORK.STUFF) using only statements within the data step that adheres to the following conditions.

1. The data set must only hold observations including "Spain" (Can be observed in Team1_Name and Team2_Name variables).

2. The data set must have only one observation with two variables ("S_Goals" and "O_Goals" -- Spain goals and opponent goals).

     "S_Goals" is the average number of goals scored by Spain per game. "O_Goals" is the average number of goals scored by the opponents of Spain per game.

Anyone have any clue? The only thing I can come up with this splitting the data where Spain is either Team1_Name or Team2_Name and then interleaving them into a single data set. The goal is to get this result and adhere to the above guidelines.

If anyone has a clue, I'd appreciate it.

[ChrisNZ]

Is this some sort of exam you have to pass ?

In which case you'd better find out by yourself or you'll never learn.

data WORK.STUFF;

  set WORK.INFO end=LASTOBS;

  N+1;

  if Team1_Name='Spain' then do;

    S_Goals+Team1_Score;

    O_Goals+Team2_Score;

  end;

  else if Team2_Name='Spain' then do;

    S_Goals+Team2_Score;

    O_Goals+Team1_Score;

  end;

  else N+-1;

  if LASTOBS then do;

    S_Goals=S_Goals/N;

    O_Goals=O_Goals/N;

      output;

  end;

  keep O_Goals S_Goals;

run;