SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Extracting and counting data from csv

Reply
Occasional Contributor
Posts: 13

Extracting and counting data from csv

Good evening. I've got a question, obviously

 

 

i have a large data set (120k observation )

i need to find a way to count the number of times an event occurs after another ever 

 

example

 

evrytime there is a rows in my data is labeled with "O" in a colum, I need to count how many time the row directly below equals "2FGA" 

 

there is another column that represents a name with each row. 

If anyone knows how it would be possible to count how many time name associated with the "O" was counted would be great. 

 

Thanks for the help

 

ask me questoins if im being too broad. 

PROC Star
Posts: 7,364

Re: Extracting and counting data from csv

Not sure if I understand what you have but, hopefully, the following comes close:

 

data have;
  length event $4;
  set sashelp.class (keep=name);
  if _n_ in (2,4,6,8,10,12,14,16,18) then event='O';
  if _n_ in (3,7,11,13,17,19) then event='2FGA';
run;

data want (drop=last);
  set have;
  last=lag(event);
  if event='2FGA' and last eq 'O' then output;
run;

The resulting file, want, has 6 records which is the count I think you are looking for.

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 13

Re: Extracting and counting data from csv

I'll give it a try but to try and make myself more clear i'm attaching an example of the data.

 

So you'll see 5 columns

 

1st (no title) / 2nd (team_name) / 3rd (player_name) / 4th (event_desc_id) / 5th (event_desc)

 

so basically there will be values in the 'event_desc_id' column such as 2FGAB, AG, FV, D, O, 3FGA, 2FGA

I would like to count the number of times that the row directly after an 'O' has a '3FGA' or a 2FGA'

 

hopefully that will make a bit more sense 

 

PROC Star
Posts: 7,364

Re: Extracting and counting data from csv

Did you try the method I suggested? To repeat, :

 

data want (drop=last);
  set have;
  last=lag(event);
  if event in ('2FGA','3FGA') and last eq 'O' then output;
run;
Occasional Contributor
Posts: 13

Re: Extracting and counting data from csv

When I run the results nothing pops up it does straight to the output tab

Occasional Contributor
Posts: 13

Re: Extracting and counting data from csv

i couldn't figure it out with the code you suggested.  Sorry i'm still new to sas.

All i did was import the data and then followed with what you wrote but clearly that wasn't correct.

PROC Star
Posts: 7,364

Re: Extracting and counting data from csv

post the code you ran

Occasional Contributor
Posts: 13

Re: Extracting and counting data from csv

so far this is all i have. 

 

 

proc import datafile="/home/kcmitche/DataChallengeEuroGamePBP-2.csv"
out=EuroBB dbms=csv replace;
run;

data want (drop=last);
set have;
last=lag(event);
if event in ('2FGA','3FGA') and last eq 'O' then output;
run;

 

 

again rookie here

PROC Star
Posts: 7,364

Re: Extracting and counting data from csv

And, can you view file WANT? If I correctly understand what you wanted, the number of records in that file is the answer you were seeking.

 

Art, CEO, AnalystFinder.com

 

Occasional Contributor
Posts: 13

Re: Extracting and counting data from csv

[ Edited ]

So I'm trying to count the number of times that a selected variables happen directly after an 'O'.  

 

For example in the sample data.

row 25 has 'O' and directly below that row 26 has a '2FGA'

 

I want to find a way to show how many times that the player_name associated with an 'O' resulted in a 2FGA or 3FGA directly after the 'O'.  I don't care about the player_name in the following row or in the row with 'O'

 

 

try and explain differently

in row 25. 

Honeycutt, Tyler = 1 'O' (because directly after there was a 2FGA

 

next would be 

Rice, Tyrese (row 29)

he would = 1 because there was a 3FGM after.

 

does that make more sense.

 

Occasional Contributor
Posts: 13

Re: Extracting and counting data from csv

I'd like to make a table showing me how many times this happened to each player.

'O' stands for offensive rebounds.

 

Basically i would like to find a way to find the top 5 players whose offensive rebounds resulted in an attempted shot on the basket

Ask a Question
Discussion stats
  • 10 replies
  • 3693 views
  • 0 likes
  • 2 in conversation