SPSSX Discussion

Selecting cases with variable numbers of records per case

Classic

List

Threaded

5 messages Options

JeremyT

Selecting cases with variable numbers of records per case

The data looks like this. Event Record Last Type Par1 Par2 Par3 1 1 0 L 0.12 0.03 0.76 1 2 0 L 0.92 0.63 0.24 1 3 0 S 0.95 0.96 0.96 1 4 1 H 0.89 0.22 0.26 2 1 0 S 0.87 0.83 0.33 2 2 1 S 0.99 0.57 0.81 3 1 1 H 0.62 0.08 0.41 4 1 0 L 0.58 0.90 0.80 4 2 0 L 0.85 0.29 0.11 4 3 0 S 0.41 0.06 0.85 4 4 0 S 0.01 0.75 0.71 4 5 1 S 0.59 0.03 0.58 Record ranges from 1-151, i.e there are up to 151 multiple records at each event. I need to select all records for each event where Last= 1 and Type = H, i.e. I want to be left with a dataset thus: Event Record Last Type Par1 Par2 Par3 1 1 0 L 0.12 0.03 0.76 1 2 0 L 0.92 0.63 0.24 1 3 0 S 0.95 0.96 0.96 1 4 1 H 0.89 0.22 0.26 3 1 1 H 0.62 0.08 0.41 There are about 16 million rows of data and about 10,000 events where Last = 1 and Type = H. Any advice will be most welcome

JeremyT

Re: Selecting cases with variable numbers of records per case

Whoops, that does not look right! Try again....

The data looks like this.

Event, Record, Last, Type, Par1, Par2, Par3
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
2, 1, 0, S, 0.87, 0.83, 0.33
2, 2, 1, S, 0.99, 0.57, 0.81
3, 1, 1, H, 0.62, 0.08, 0.41
4, 1, 0, L, 0.58, 0.90, 0.80
4, 2, 0, L, 0.85, 0.29, 0.11
4, 3, 0, S, 0.41, 0.06, 0.85
4, 4, 0, S, 0.01, 0.75, 0.71
4, 5, 1, S, 0.59, 0.03, 0.58

Record ranges from 1-151, i.e there are up to 151 multiple records at each event. I need to select all records for each event where Last= 1 and Type = H, i.e. I want to be left with a dataset thus:

Event, Record, Last, Type, Par1, Par2, Par3
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
3, 1, 1, H, 0.62, 0.08, 0.41

There are about 16 million rows of data and about 10,000 events where Last = 1 and Type = H. Any advice will be most welcome

Bruce Weaver

Re: Selecting cases with variable numbers of records per case

Administrator

What have you tried so far? Have you looked at SELECT IF in the fine manual, for example?

JeremyT wrote

Whoops, that does not look right! Try again....

The data looks like this.

Event, Record, Last, Type, Par1, Par2, Par3
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
2, 1, 0, S, 0.87, 0.83, 0.33
2, 2, 1, S, 0.99, 0.57, 0.81
3, 1, 1, H, 0.62, 0.08, 0.41
4, 1, 0, L, 0.58, 0.90, 0.80
4, 2, 0, L, 0.85, 0.29, 0.11
4, 3, 0, S, 0.41, 0.06, 0.85
4, 4, 0, S, 0.01, 0.75, 0.71
4, 5, 1, S, 0.59, 0.03, 0.58

Record ranges from 1-151, i.e there are up to 151 multiple records at each event. I need to select all records for each event where Last= 1 and Type = H, i.e. I want to be left with a dataset thus:

Event, Record, Last, Type, Par1, Par2, Par3
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
3, 1, 1, H, 0.62, 0.08, 0.41

There are about 16 million rows of data and about 10,000 events where Last = 1 and Type = H. Any advice will be most welcome

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

David Marso

Re: Selecting cases with variable numbers of records per case

Administrator

DATA LIST LIST/ Event (F) Record (F) Last (F) Type (A) Par1 (F8.3) Par2 (F8.3) Par3 (F8.3) .
begin data
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
2, 1, 0, S, 0.87, 0.83, 0.33
2, 2, 1, S, 0.99, 0.57, 0.81
3, 1, 1, H, 0.62, 0.08, 0.41
4, 1, 0, L, 0.58, 0.90, 0.80
4, 2, 0, L, 0.85, 0.29, 0.11
4, 3, 0, S, 0.41, 0.06, 0.85
4, 4, 0, S, 0.01, 0.75, 0.71
4, 5, 1, S, 0.59, 0.03, 0.58
end data.

** If you have the data in a system file then skip SAVE and substitute in MATCH.
** Assuming it is ordered by event.
SAVE OUTFILE 'rawdata'.
SELECT IF LAST AND TYPE="H".
COMPUTE RETAIN=1.
MATCH FILES / FILE 'rawdata'/TABLE * / BY event.
SELECT IF RETAIN.
EXE.

Bruce Weaver wrote

What have you tried so far? Have you looked at SELECT IF in the fine manual, for example?

JeremyT wrote

Whoops, that does not look right! Try again....

The data looks like this.

Event, Record, Last, Type, Par1, Par2, Par3
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
2, 1, 0, S, 0.87, 0.83, 0.33
2, 2, 1, S, 0.99, 0.57, 0.81
3, 1, 1, H, 0.62, 0.08, 0.41
4, 1, 0, L, 0.58, 0.90, 0.80
4, 2, 0, L, 0.85, 0.29, 0.11
4, 3, 0, S, 0.41, 0.06, 0.85
4, 4, 0, S, 0.01, 0.75, 0.71
4, 5, 1, S, 0.59, 0.03, 0.58

Record ranges from 1-151, i.e there are up to 151 multiple records at each event. I need to select all records for each event where Last= 1 and Type = H, i.e. I want to be left with a dataset thus:

Event, Record, Last, Type, Par1, Par2, Par3
1, 1, 0, L, 0.12, 0.03, 0.76
1, 2, 0, L, 0.92, 0.63, 0.24
1, 3, 0, S, 0.95, 0.96, 0.96
1, 4, 1, H, 0.89, 0.22, 0.26
3, 1, 1, H, 0.62, 0.08, 0.41

There are about 16 million rows of data and about 10,000 events where Last = 1 and Type = H. Any advice will be most welcome

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

JeremyT

Re: Selecting cases with variable numbers of records per case

Thanks David, it was the Match Files trick that had eluded me.