Using SQL in SPSS

[Anthony]

Hi, I've been using SQL for years but am still relatively new to
SPSS. Is there any way to efficiently manipulate SPSS data sets with
SQL commands instead of SPSS' native language? - Doing this while
running Python would be even better. I did some searches, and every
result that had SPSS and SQL on it was related to Import/Export
procedures, not straight data manipulation. I realize that up until
one or two versions ago, SPSS couldn't even handle multiple data sets
running in the same instance, so I doubt I'll have much luck with
this.

Thanks

[JKPeck]

SPSS SQL direct support is limited to retrieving and saving to
external databases. However, if you want to use Python in SPSS and
SQL, you can pull your active data into Python and use its standard
SQL database (and then put the data back in SPSS).

Here is a short example that creates a database, populates it, and
then retrieves some cases. This is a very simple example, but it
should give you the idea. If you have spss 16, you can use the spss
module Dataset class instead of the spssdata module to pull the data,
but spssdata will still work.

HTH,
Jon Peck

#sqlite database from SPSS data

import spss, spssaux, spssdata, sqlite3
import time

spssaux.OpenDataFile("c:/temp/50vars100000cases.sav")
con = sqlite3.connect("c:/temp/50vars1000000cases.db")

# define access to SPSS active dataset
spsscur = spssdata.Spssdata(names=False)
def spssget():
for i, case in enumerate(spsscur):
yield (i+1,)+case

sqlcur = con.cursor()

#create the sqlite database
varlist = ",".join(["V"+str(i+1) +" float " for i in range(50)])
qlist = ",".join(51*"?")
sqlcur.execute("create table random (casenum int primary key, " +
varlist + ")")

#populate the database
t0 = time.time()
sqlcur.executemany("insert into random values(" + qlist + ")",
spssget())
con.commit()
t1 = time.time()
print "insert time:", t1-t0

# select a few records
sqlcur.execute("select casenum v1, v5, v10 from random where
random.casenum IN (50000, 20000,70000)")
for row in sqlcur:
print row

# display db info
sqlcur.execute("select * from sqlite_master where type in ('table',
'view')")
print sqlcur.fetchall()

con.close()

[news]

My trick is to save data as dbf files, then use a fancy sql statement to
join and merge them. Windows comes with a good dbf sql engine that allows
fancy sql statements. Its very quick.

"JKPeck" <JKP...@gmail.com> wrote in message
news:8c3fa2a7-a32a-49f2...@a1g2000hsb.googlegroups.com...

[RJ2]

This is interesting....how do you do this fancysqlstatement with
Windows/dbf? Are your commands sent through SPSS???

On Jul 23, 11:46 pm, "news" <n...@news.org.invalid> wrote:
> My trick is to save data as dbf files, then use a fancysqlstatement to

> join and merge them. Windows comes with a good dbfsqlengine that allows

> fancysqlstatements. Its very quick.

>
> "JKPeck" <JKP...@gmail.com> wrote in message
>
> news:8c3fa2a7-a32a-49f2...@a1g2000hsb.googlegroups.com...
>
>
>
> > On Jul 23, 6:56 am, Anthony <ajdam...@gmail.com> wrote:

> >> Hi, I've been usingSQLfor years but am still relatively new to

> >> SPSS.  Is there any way to efficiently manipulate SPSS data sets with

> >>SQLcommands instead of SPSS' native language? - Doing this while
> >> runningPythonwould be even better.  I did some searches, and every
> >> result that had SPSS andSQLon it was related to Import/Export

> >> procedures, not straight data manipulation.  I realize that up until
> >> one or two versions ago, SPSS couldn't even handle multiple data sets
> >> running in the same instance, so I doubt I'll have much luck with
> >> this.
>
> >> Thanks
>

> > SPSSSQLdirect support is limited to retrieving and saving to
> > external databases.  However, if you want to usePythonin SPSS and

> >SQL, you can pull your active data intoPythonand use its standard

> >SQLdatabase (and then put the data back in SPSS).

> > con.close()- Hide quoted text -
>
> - Show quoted text -

[news]

Yes, and I get back a SPSS dataset. There are situations where a SQL merge
would be very difficult to program in SPSS.

"RJ2" <richardjam...@gmail.com> wrote in message
news:a1a4e45d-684b-40c5...@k36g2000pri.googlegroups.com...

[RJ2]

would you have a short code sample? how are you setup - i.e. what db
engine did you install?

On Jul 26, 6:59 pm, "news" <n...@news.org.invalid> wrote:
> Yes, and I get back a SPSS dataset. There are situations where a SQL merge
> would be very difficult to program in SPSS.
>

> "RJ2" <richardjamesresea...@gmail.com> wrote in message

> > - Show quoted text -- Hide quoted text -