SPSS and/or Queries

[Craig Johnson]

I'm working with a large data set that we use daily.  Normally we pull
group level data based off a group ID.  However sometimes we need to
pull mulitple groups at a time and/or we need to add additional query
paramaders to narrow our search.  So.........basically I need to know the
SPSS syntax for conducting AND/OR queries.  Can someone give me an
example of how to do this through the SPSS syntax and/or provide me a
good link for this info.  I have yet to find anything....

Here is the sort of generic equivalent SQL OR query I'm trying to
replicate...

SELECT Jobs.Source, Jobs.Resume, Jobs.[Sent Materials], Jobs.[Applied
Date], Jobs.[Reply Date]
FROM Jobs
WHERE (((Jobs.Source)="Hot JObs" Or (Jobs.Source)="Career Builder" Or
(Jobs.Source)="Monster"));

Here is a the sort of generic equivalent SQL AND query I'm trying to
replicate.....

SELECT Books.Title, Books.[Last Name]
FROM Books
WHERE (((Books.Title)="Good to Great") AND ((Books.[Last Name])="Jim
Collins"));
Thanks!

[Richard Ristow]

At 07:37 PM 5/1/2007, Craig Johnson wrote:

>I need to know the SPSS syntax for conducting AND/OR queries.  Can
>someone give me an example of how to do this through the SPSS syntax

As a quick and likely partial answer, the SPSS "SELECT IF" statement is
a close equivalent to the SQL "WHERE" clause. The test can use full
Boolean logic; see subsections "Logical expressions" and "Logical
functions" in the Command Syntax Reference (it's on the installation
CD, as 'spssbase.pdf').

>Here is the sort of generic equivalent SQL OR query I'm trying to
>replicate...
>
>SELECT Jobs.Source, Jobs.Resume, Jobs.[Sent Materials], Jobs.[Applied
>Date], Jobs.[Reply Date]
>FROM Jobs
>WHERE (((Jobs.Source)="Hot JObs" Or (Jobs.Source)="Career Builder" Or
>(Jobs.Source)="Monster"));

The Boolean syntax should carry over almost exactly. In fact, I think
exactly, though a lot of people's SPSS style wouldn't write it exactly
the same way. (For example, parentheses around the variable name do
nothing for you and wouldn't ordinarily be used; there's a school of
thought that recommends "EQ" rather than "=" for equality comparison;
and parentheses around the whole expression aren't necessary.) But
here's your syntax essentially unchanged; I haven't tested it, but I
think it should work. The line breaks and aligning of clauses are my
taste for readability, not a syntactic requirement.

SELECT IF   ((Jobs.Source)="Hot JObs"
           Or (Jobs.Source)="Career Builder"
           Or (Jobs.Source)="Monster").

Or, for "OR" tests, the "ANY" function is your friend. Sstill untested,
and line breaks still for my aesthetic taste:

SELECT IF   ANY(Jobs.Source,
                "Hot JObs","Career Builder","Monster").

Comparisons are case-sensitive; if that's not desired, use the UPCASE
string function (on both comparands) before comparison.

>Here is a the sort of generic equivalent SQL AND query I'm trying to
>replicate.....
>
>SELECT Books.Title, Books.[Last Name]
>FROM Books
>WHERE (((Books.Title)="Good to Great") AND ((Books.[Last Name])="Jim
>Collins"));

Again, it should carry over practically unchanged. I'm removing
redundant parentheses, and the code is still untested:

SELECT IF  Books.Title     ="Good to Great"
       AND  Books.Last_Name ="Jim Collins";

Note change from field names to SPSS variable names. All the field
names are valid as SPSS variable names, except "Books.[Last Name]",
which I've changed to "Books.Last_Name".

-Good wishes and good luck,
  Richard