topic Re: Extracting and counting data from csv in SAS Data Management

Extracting and counting data from csv

kcmitche — Wed, 15 Mar 2017 04:18:29 GMT

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.

Re: Extracting and counting data from csv

art297 — Wed, 15 Mar 2017 04:31:20 GMT

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

Re: Extracting and counting data from csv

kcmitche — Thu, 16 Mar 2017 00:38:00 GMT

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

Re: Extracting and counting data from csv

art297 — Thu, 16 Mar 2017 00:43:39 GMT

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;

Re: Extracting and counting data from csv

kcmitche — Thu, 16 Mar 2017 00:43:56 GMT

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.

Re: Extracting and counting data from csv

kcmitche — Thu, 16 Mar 2017 00:45:01 GMT

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

Re: Extracting and counting data from csv

art297 — Thu, 16 Mar 2017 00:46:03 GMT

post the code you ran

Re: Extracting and counting data from csv

kcmitche — Thu, 16 Mar 2017 00:47:44 GMT

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

Re: Extracting and counting data from csv

art297 — Thu, 16 Mar 2017 00:49:44 GMT

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

Re: Extracting and counting data from csv

kcmitche — Thu, 16 Mar 2017 00:57:07 GMT

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.

Re: Extracting and counting data from csv

kcmitche — Thu, 16 Mar 2017 00:58:39 GMT

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