SAS first.var last.var equivalent?

[Muenchen, Robert A (Bob)]

Hi All,

Is there an SPSS equivalent to the SAS first.var or last.var function?
That allows you to select the first or last observation in a group. You
might have a dataset sorted by division then department and want just
the first or last record of each department (each division might have
departments with the same name).

In SAS that would be:

Data lastonly; set all;
By division department;
If last.department;
Run;

What's the easiest way to do this in SPSS?

Thanks,
Bob

=========================================================
  Bob Muenchen (pronounced Min'-chen), Manager
  Statistical Consulting Center
  U of TN Office of Information Technology
  200 Stokely Management Center, Knoxville, TN 37996-0520
  Voice: [phone]
  FAX:   [phone]
  Email: [hidden email]
  Web:   http://oit.utk.edu/scc,
  News:  http://listserv.utk.edu/archives/statnews.html
=========================================================

[Melissa Ives]

Look at AGGREGATE.  I believe it is what you want.

Melissa
The bubbling brook would lose its song if you removed the rocks.


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Muenchen, Robert A (Bob)
Sent: Wednesday, March 21, 2007 10:29 AM
To: [hidden email]
Subject: [SPSSX-L] SAS first.var last.var equivalent?

Hi All,

Is there an SPSS equivalent to the SAS first.var or last.var function?
That allows you to select the first or last observation in a group. You
might have a dataset sorted by division then department and want just
the first or last record of each department (each division might have
departments with the same name).

In SAS that would be:

Data lastonly; set all;
By division department;
If last.department;
Run;

What's the easiest way to do this in SPSS?

Thanks,
Bob

=========================================================
  Bob Muenchen (pronounced Min'-chen), Manager
  Statistical Consulting Center
  U of TN Office of Information Technology
  200 Stokely Management Center, Knoxville, TN 37996-0520
  Voice: [phone]
  FAX:   [phone]
  Email: [hidden email]
  Web:   http://oit.utk.edu/scc,
  News:  http://listserv.utk.edu/archives/statnews.html
=========================================================


PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

[Peck, Jon]

AGGREGATE using the FIRST or LAST function.

For example,
DATASET DECLARE firstonly.
AGGREGATE /outfile='firstonly'
/break jobcat
/salary_first = FIRST(salary).

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Muenchen, Robert A (Bob)
Sent: Wednesday, March 21, 2007 10:29 AM
To: [hidden email]
Subject: [SPSSX-L] SAS first.var last.var equivalent?

Hi All,

Is there an SPSS equivalent to the SAS first.var or last.var function?
That allows you to select the first or last observation in a group. You
might have a dataset sorted by division then department and want just
the first or last record of each department (each division might have
departments with the same name).

In SAS that would be:

Data lastonly; set all;
By division department;
If last.department;
Run;

What's the easiest way to do this in SPSS?

Thanks,
Bob

=========================================================
  Bob Muenchen (pronounced Min'-chen), Manager
  Statistical Consulting Center
  U of TN Office of Information Technology
  200 Stokely Management Center, Knoxville, TN 37996-0520
  Voice: [phone]
  FAX:   [phone]
  Email: [hidden email]
  Web:   http://oit.utk.edu/scc,
  News:  http://listserv.utk.edu/archives/statnews.html
=========================================================

[Beadle, ViAnn]

Check out the first and last functions available in AGGREGATE and FIRST and LAST commands in MATCH FILES.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Muenchen, Robert A (Bob)
Sent: Wednesday, March 21, 2007 10:29 AM
To: [hidden email]
Subject: SAS first.var last.var equivalent?

Hi All,

Is there an SPSS equivalent to the SAS first.var or last.var function?
That allows you to select the first or last observation in a group. You
might have a dataset sorted by division then department and want just
the first or last record of each department (each division might have
departments with the same name).

In SAS that would be:

Data lastonly; set all;
By division department;
If last.department;
Run;

What's the easiest way to do this in SPSS?

Thanks,
Bob

=========================================================
  Bob Muenchen (pronounced Min'-chen), Manager
  Statistical Consulting Center
  U of TN Office of Information Technology
  200 Stokely Management Center, Knoxville, TN 37996-0520
  Voice: [phone]
  FAX:   [phone]
  Email: [hidden email]
  Web:   http://oit.utk.edu/scc,
  News:  http://listserv.utk.edu/archives/statnews.html
=========================================================

[Oliver, Richard]

The MATCH FILES solution is probably the simplest solution when you have many variables (since AGGREGATE would require that you specify an aggregate function for every non-break variable), but it's not all that obvious until you realize that it's perfectly legal to run the command on one dataset and that can be the active dataset, as in:

data list  free /id (f2) purchaseDate (adate10) amount (f3).
begin data
1 1/1/2007 111
1 1/1/2004 121
1 1/1/2005 131
2 2/1/2006 211
2 2/1/2007 221
2 2/1/2003 231
end data.
sort cases by id purchaseDate.
match files file=*  /by id  /last=lastdate.
select if lastdate=1.
execute. /*to see results immediately.



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Beadle, ViAnn
Sent: Wednesday, March 21, 2007 10:55 AM
To: [hidden email]
Subject: Re: SAS first.var last.var equivalent?

Check out the first and last functions available in AGGREGATE and FIRST and LAST commands in MATCH FILES.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Muenchen, Robert A (Bob)
Sent: Wednesday, March 21, 2007 10:29 AM
To: [hidden email]
Subject: SAS first.var last.var equivalent?

Hi All,

Is there an SPSS equivalent to the SAS first.var or last.var function?
That allows you to select the first or last observation in a group. You
might have a dataset sorted by division then department and want just
the first or last record of each department (each division might have
departments with the same name).

In SAS that would be:

Data lastonly; set all;
By division department;
If last.department;
Run;

What's the easiest way to do this in SPSS?

Thanks,
Bob

=========================================================
  Bob Muenchen (pronounced Min'-chen), Manager
  Statistical Consulting Center
  U of TN Office of Information Technology
  200 Stokely Management Center, Knoxville, TN 37996-0520
  Voice: [phone]
  FAX:   [phone]
  Email: [hidden email]
  Web:   http://oit.utk.edu/scc,
  News:  http://listserv.utk.edu/archives/statnews.html
=========================================================

[Richard Ristow]

At 12:51 PM 3/21/2007, Oliver, Richard wrote:

>The MATCH FILES solution is probably the simplest solution when you
>have many variables (since AGGREGATE would require that you specify an
>aggregate function for every non-break variable).

Besides, I think it would give the wrong answer for any variable that's
missing in the first record: AGGREGATE's 'FIRST' function takes the
first >non-missing< value. (Don't complain. This is often extremely
useful.)

>but it's not all that obvious until you realize that it's perfectly
>legal to run [MATCH FILES - or ADD FILES, for that matter] on one
>dataset and that can be the active dataset

Exactly. An extremely useful trick. (Now, do you know how many of us
hot-shots won't be able to show off by using it to solve people's
problems? :-P)

Richard, should this one go in the manual or Help entries for ADD FILES
and MATCH FILES?

>data list  free /id (f2) purchaseDate (adate10) amount (f3).
>begin data
>1 1/1/2007 111
>1 1/1/2004 121
>1 1/1/2005 131
>2 2/1/2006 211
>2 2/1/2007 221
>2 2/1/2003 231
>end data.
>sort cases by id purchaseDate.
>match files file=*  /by id  /last=lastdate.
>select if lastdate=1.
>execute. /*to see results immediately.

Coding and posting style: Suggest, instead of 'execute',

LIST     /*to see results in the listing */.

[Richard Ristow]

At 11:29 AM 3/21/2007, Muenchen, Robert A (Bob) wrote:

>Is there an SPSS equivalent to the SAS first.var or last.var function?

As pointed out, the closest equivalents are the /FIRST and /LAST
variables specified on the MATCH FILES or ADD FILES commands.

But (sigh), they're 'closest equivalents', in that they're sadly
inferior to the SAS 'first.' and 'last.' prefixes. In SAS, you can test
for a break on any level of a multi-variable key. In SPSS, you can test
only for the lowest-level break.

A lesser difference is that the SAS 'first.' and 'last.' variables
aren't saved in the output; SPSS /FIRST and /LAST variables are. That
matters, because those variables are very often wanted in computations
but not in output. Accepting scratch variables in those roles (and for
/IN variables) would be a modest, but very useful, extension.

[Ornelas, Fermin]

SAS also allows you to make first and last permanent if you want to.
But this is not a forum to compare one product to another.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Richard Ristow
Sent: Wednesday, March 21, 2007 11:13 AM
To: [hidden email]
Subject: Re: SAS first.var last.var equivalent?

At 11:29 AM 3/21/2007, Muenchen, Robert A (Bob) wrote:

>Is there an SPSS equivalent to the SAS first.var or last.var function?

As pointed out, the closest equivalents are the /FIRST and /LAST
variables specified on the MATCH FILES or ADD FILES commands.

But (sigh), they're 'closest equivalents', in that they're sadly
inferior to the SAS 'first.' and 'last.' prefixes. In SAS, you can test
for a break on any level of a multi-variable key. In SPSS, you can test
only for the lowest-level break.

A lesser difference is that the SAS 'first.' and 'last.' variables
aren't saved in the output; SPSS /FIRST and /LAST variables are. That
matters, because those variables are very often wanted in computations
but not in output. Accepting scratch variables in those roles (and for
/IN variables) would be a modest, but very useful, extension.

NOTICE: This e-mail (and any attachments) may contain PRIVILEGED OR
CONFIDENTIAL information and is intended only for the use of the
specific individual(s) to whom it is addressed.  It may contain
information that is privileged and confidential under state and federal
law.  This information may be used or disclosed only in accordance with
law, and you may be subject to penalties under law for improper use or
further disclosure of the information in this e-mail and its
attachments. If you have received this e-mail in error, please
immediately notify the person named above by reply e-mail, and then
delete the original e-mail.  Thank you.

[Oliver, Richard]

Re: "should this one go in the manual or Help entries for ADD FILES and MATCH FILES?"

A similar example is already included in the MATCH FILES documentation, in the section on the FIRST and LAST subcommands:

* Using match files with only one file.
* This example flags the first of several cases with
the same value for a key variable.

MATCH FILES FILE='c:\data\persons.sav'
 /BY HOUSEID /FIRST=HEAD.
SELECT IF (HEAD EQ 1).
CROSSTABS JOBCAT BY SEX.

MATCH FILES is used instead of GET to read the SPSS-format data file persons.sav. The BY subcommand identifies the key variable (HOUSEID), and FIRST creates the variable HEAD with the value 1 for the first case in each household and the value 0 for all other cases.

SELECT IF selects only the cases with the value 1 for HEAD, and the CROSSTABS procedure is run on these cases.

-----Original Message-----
From: Richard Ristow [mailto:[hidden email]]
Sent: Wednesday, March 21, 2007 1:04 PM
To: Oliver, Richard; [hidden email]
Cc: Muenchen, Robert A (Bob)
Subject: Re: SAS first.var last.var equivalent?

At 12:51 PM 3/21/2007, Oliver, Richard wrote:

>The MATCH FILES solution is probably the simplest solution when you
>have many variables (since AGGREGATE would require that you specify an
>aggregate function for every non-break variable).

Besides, I think it would give the wrong answer for any variable that's
missing in the first record: AGGREGATE's 'FIRST' function takes the
first >non-missing< value. (Don't complain. This is often extremely
useful.)

>but it's not all that obvious until you realize that it's perfectly
>legal to run [MATCH FILES - or ADD FILES, for that matter] on one
>dataset and that can be the active dataset

Exactly. An extremely useful trick. (Now, do you know how many of us
hot-shots won't be able to show off by using it to solve people's
problems? :-P)

Richard, should this one go in the manual or Help entries for ADD FILES
and MATCH FILES?

>data list  free /id (f2) purchaseDate (adate10) amount (f3).
>begin data
>1 1/1/2007 111
>1 1/1/2004 121
>1 1/1/2005 131
>2 2/1/2006 211
>2 2/1/2007 221
>2 2/1/2003 231
>end data.
>sort cases by id purchaseDate.
>match files file=*  /by id  /last=lastdate.
>select if lastdate=1.
>execute. /*to see results immediately.

Coding and posting style: Suggest, instead of 'execute',

LIST     /*to see results in the listing */.

[Richard Ristow]

Dear Richard,

At 03:23 PM 3/21/2007, you wrote:

>>Re: "should this one go in the manual or Help entries for ADD FILES
>>and MATCH FILES?"
>
>A similar example is already included in the M[ATCH FILES
>documentation, in the section on the FIRST and LAST subcommands:
>
>Example
>* Using match files with only one file.

Absolutely so, and I should have checked more carefully.

Thank you!
Richard

[Muenchen, Robert A (Bob)]

Great! Thanks to all who replied. I had checked documentation & Googled
that the topic but I skipped the references to ADD FILES since I thought
I knew that procedure thoroughly. Live & learn!

Cheers,
Bob

=========================================================
  Bob Muenchen (pronounced Min'-chen), Manager
  Statistical Consulting Center
  U of TN Office of Information Technology
  200 Stokely Management Center, Knoxville, TN 37996-0520
  Voice: [phone]
  FAX:   [phone]
  Email: [hidden email]
  Web:   http://oit.utk.edu/scc,
  News:  http://listserv.utk.edu/archives/statnews.html
=========================================================


> -----Original Message-----
> From: Oliver, Richard [mailto:[hidden email]]
> Sent: Wednesday, March 21, 2007 3:24 PM
> To: Richard Ristow; [hidden email]
> Cc: Muenchen, Robert A (Bob)
> Subject: RE: SAS first.var last.var equivalent?
>
> Re: "should this one go in the manual or Help entries for ADD FILES
and
> MATCH FILES?"
>
> A similar example is already included in the MATCH FILES
documentation,

> in the section on the FIRST and LAST subcommands:
>
> * Using match files with only one file.
> * This example flags the first of several cases with
> the same value for a key variable.
>
> MATCH FILES FILE='c:\data\persons.sav'
>  /BY HOUSEID /FIRST=HEAD.
> SELECT IF (HEAD EQ 1).
> CROSSTABS JOBCAT BY SEX.
>
> MATCH FILES is used instead of GET to read the SPSS-format data file
> persons.sav. The BY subcommand identifies the key variable (HOUSEID),
> and FIRST creates the variable HEAD with the value 1 for the first

case

> in each household and the value 0 for all other cases.
>
> SELECT IF selects only the cases with the value 1 for HEAD, and the
> CROSSTABS procedure is run on these cases.
>
> -----Original Message-----
> From: Richard Ristow [mailto:[hidden email]]
> Sent: Wednesday, March 21, 2007 1:04 PM
> To: Oliver, Richard; [hidden email]
> Cc: Muenchen, Robert A (Bob)
> Subject: Re: SAS first.var last.var equivalent?
>
> At 12:51 PM 3/21/2007, Oliver, Richard wrote:
>
> >The MATCH FILES solution is probably the simplest solution when you
> >have many variables (since AGGREGATE would require that you specify

an
> >aggregate function for every non-break variable).
>
> Besides, I think it would give the wrong answer for any variable
that's

> missing in the first record: AGGREGATE's 'FIRST' function takes the
> first >non-missing< value. (Don't complain. This is often extremely
> useful.)
>
> >but it's not all that obvious until you realize that it's perfectly
> >legal to run [MATCH FILES - or ADD FILES, for that matter] on one
> >dataset and that can be the active dataset
>
> Exactly. An extremely useful trick. (Now, do you know how many of us
> hot-shots won't be able to show off by using it to solve people's
> problems? :-P)
>
> Richard, should this one go in the manual or Help entries for ADD

FILES

> and MATCH FILES?
>
> >data list  free /id (f2) purchaseDate (adate10) amount (f3).
> >begin data
> >1 1/1/2007 111
> >1 1/1/2004 121
> >1 1/1/2005 131
> >2 2/1/2006 211
> >2 2/1/2007 221
> >2 2/1/2003 231
> >end data.
> >sort cases by id purchaseDate.
> >match files file=*  /by id  /last=lastdate.
> >select if lastdate=1.
> >execute. /*to see results immediately.
>
> Coding and posting style: Suggest, instead of 'execute',
>
> LIST     /*to see results in the listing */.