Selecting by date

[D.R. Wahlgren]

Gang,
I imported data from Excel into SPSS (Mac v 11.0.4).  I would like to
select a subset of cases based on values in a date format variable
(collecti) on or after April 21 of this year.

I have tried:

SELECT IF(collecti >= (YRMODA(2006,4,21))).

and

SELECT IF(collecti >= (21-APR-2006)).

The former runs but does not actually select any cases.  The latter
does not run but gives an error message.  I've looked at the "Date
and Time Functions" page in the help file but it is just too cryptic
for me to make heads or tails of.

Any advice appreciated.
thanks!
_dennis
--

```````````''''''''''''
Dennis R. Wahlgren, M.A.

Center for Behavioral Epidemiology and Community Health
San Diego State University
http://www.cbeach.org


"Poets say science takes away from the beauty of the stars--mere
globs of gas atoms.  Nothing is 'mere.'  I too can see the stars on a
desert night, and feel them.  But do I see less or more?"
--Richard Feynman

[Oliver, Richard]

Try:
 
select if collecti >=date.mdy(4,21,2006).

________________________________

From: SPSSX(r) Discussion on behalf of D.R. Wahlgren
Sent: Mon 7/31/2006 4:37 PM
To: [hidden email]
Subject: Selecting by date



Gang,
I imported data from Excel into SPSS (Mac v 11.0.4).  I would like to
select a subset of cases based on values in a date format variable
(collecti) on or after April 21 of this year.

I have tried:

SELECT IF(collecti >= (YRMODA(2006,4,21))).

and

SELECT IF(collecti >= (21-APR-2006)).

The former runs but does not actually select any cases.  The latter
does not run but gives an error message.  I've looked at the "Date
and Time Functions" page in the help file but it is just too cryptic
for me to make heads or tails of.

Any advice appreciated.
thanks!
_dennis
--

```````````''''''''''''
Dennis R. Wahlgren, M.A.

Center for Behavioral Epidemiology and Community Health
San Diego State University
http://www.cbeach.org


"Poets say science takes away from the beauty of the stars--mere
globs of gas atoms.  Nothing is 'mere.'  I too can see the stars on a
desert night, and feel them.  But do I see less or more?"
--Richard Feynman

[Richard Ristow]

At 03:37 PM 7/31/2006, D.R. Wahlgren wrote:

>I imported data from Excel into SPSS (Mac v 11.0.4).

First of all: were the Excel dates imported into SPSS correctly as SPSS
dates? It sounds like they were; but if not, there's another layer of
difficulty.

>I would like to select a subset of cases based on values in a date
>format variable (collecti) on or after April 21 of this year.
>
>I have tried:
>
>SELECT IF(collecti >= (21-APR-2006)).

No hope for this one. Alas, SPSS doesn't support date constants like
'21-APR-2006'. Large sigh.

>and
>
>SELECT IF(collecti >= (YRMODA(2006,4,21))).
>
>[which] runs but does not actually select any cases.

OK, this is a 'gotcha'. YRMODA creates dates in a different
representation from the one SPSS currently uses: YRMODA returns "the
number of days since October 14, 1582"; currently, SPSS uses the number
of SECONDS since that point. Try this:

SELECT IF(collecti >= (DATE.MDY(04,21,2006)).

[Judith Saebel]

Hi Dennis,

While you're still in Excel, do several things.

1.  Order your data by the "collecti" variable.
2.  Insert two columns next to your date column.  Call one of them
"today",
    if you like, the other one something like "Lapse".
3.  In the today column, insert the function "TODAY" into each cell,
i.e.,
    apart from the heading each cell has to contain =TODAY().
    This will insert your computer's internal date into each cell.
4.  In the lapse column, insert the difference between today and
colecti.
    (e.g., =b2-b1).  This will calculate the number of days between the
two dates.  The one corresponding to the difference for 21st April can
be used in SPSS to select your cases.

HTH,

Judith

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Richard Ristow
Sent: Tuesday, 1 August 2006 17:17
To: [hidden email]
Subject: Re: Selecting by date

At 03:37 PM 7/31/2006, D.R. Wahlgren wrote:

>I imported data from Excel into SPSS (Mac v 11.0.4).

First of all: were the Excel dates imported into SPSS correctly as SPSS
dates? It sounds like they were; but if not, there's another layer of
difficulty.

>I would like to select a subset of cases based on values in a date
>format variable (collecti) on or after April 21 of this year.
>
>I have tried:
>
>SELECT IF(collecti >= (21-APR-2006)).

No hope for this one. Alas, SPSS doesn't support date constants like
'21-APR-2006'. Large sigh.

>and
>
>SELECT IF(collecti >= (YRMODA(2006,4,21))).
>
>[which] runs but does not actually select any cases.

OK, this is a 'gotcha'. YRMODA creates dates in a different
representation from the one SPSS currently uses: YRMODA returns "the
number of days since October 14, 1582"; currently, SPSS uses the number
of SECONDS since that point. Try this:

SELECT IF(collecti >= (DATE.MDY(04,21,2006)).

[D.R. Wahlgren]

Thank you to everyone who replied!  Your answers not only solved my
immediate problem, but also helped me learn more about how SPSS
handles dates and how to include the formatting commands in syntax.
Nothing better than getting a fish while learning how to fish!

:)

_dennis
--
```````````''''''''''''
Dennis R. Wahlgren, M.A.

Center for Behavioral Epidemiology and Community Health
San Diego State University
http://www.cbeach.org

"Poets say science takes away from the beauty of the stars--mere globs of
gas atoms.  Nothing is 'mere.'  I too can see the stars on a desert night,
and feel them.  But do I see less or more?"      --Richard Feynman

[D.R. Wahlgren]

At 6:10 PM -0400 8/1/06, Richard Ristow wrote:

>I wrote,
>
>>YRMODA creates dates in a different representation from the one
>>SPSS currently uses: YRMODA returns "the number of days since
>>October 14, 1582"; currently, SPSS uses the number of SECONDS since
>>that point. Try this:
>>
>>SELECT IF(collecti >= (DATE.MDY(04,21,2006)).
>
>That's the right advice, and should work; let me know. But, YRMODA
>generates, of course, *smaller* numbers than the current date-time
>functions, so it looks like the code you used should have selected
>all the cases, rather than none of them.
>
>OK, I'm muddled today. (I'm at 39,000 feet or so, en route from
>Oakland to Baltimore via Salt Lake City.) I may well be missing
>something *this* time. But, please post whether the code I posted
>before solved your problem. It SHOULD...

heh.  Yes, what you wrote the first time was correct and worked like
a charm.  I ended up using a variant of this and one bit of syntax
supplied by another listmember, which allowed me to select a range of
cases between two dates.

I wasn't at all clear from SPSS' help file how to use these various
date functions within the syntax, but with your and the others'
examples, it's helping me understand it much better.

thanks again,
Dennis
--

```````````''''''''''''
Dennis R. Wahlgren, M.A.

Center for Behavioral Epidemiology and Community Health
San Diego State University
http://www.cbeach.org


"Poets say science takes away from the beauty of the stars--mere
globs of gas atoms.  Nothing is 'mere.'  I too can see the stars on a
desert night, and feel them.  But do I see less or more?"
--Richard Feynman

[D.R. Wahlgren]

At 6:10 PM -0400 8/1/06, Richard Ristow wrote:

>A second thought. What I wrote before should be right, but -
>
>At 03:37 PM 7/31/2006, D.R. Wahlgren wrote:
>
>>>I have tried:
>>>
>>>SELECT IF(collecti >= (YRMODA(2006,4,21))).
>>>
>>>[which] runs but does not actually select any cases.
>
>I wrote,
>
>>YRMODA creates dates in a different representation from the one
>>SPSS currently uses: YRMODA returns "the number of days since
>>October 14, 1582"; currently, SPSS uses the number of SECONDS since
>>that point. Try this:
>>
>>SELECT IF(collecti >= (DATE.MDY(04,21,2006)).
>
>That's the right advice, and should work; let me know. But, YRMODA
>generates, of course, *smaller* numbers than the current date-time
>functions, so it looks like the code you used should have selected
>all the cases, rather than none of them.

Oh, and yes, that's exactly what that first attempt did (or didn't
do, depending on your perspective ;-)  When I wrote that it did "not
actually select any cases," I guess technically it *did* select all
of the cases :)

Best,
Dennis
--

```````````''''''''''''
Dennis R. Wahlgren, M.A.

Center for Behavioral Epidemiology and Community Health
San Diego State University
http://www.cbeach.org


"Poets say science takes away from the beauty of the stars--mere
globs of gas atoms.  Nothing is 'mere.'  I too can see the stars on a
desert night, and feel them.  But do I see less or more?"
--Richard Feynman