How do I export a SQL query into SPSS?

Question

I have this monster query written in T-SQL that pulls together and crunches data from multiple tables. I can export the result to CSV or Excel easily enough, but would like to send it right into SPSS. The ODBC drivers in SPSS only recognize Tables and Views in my SQL database. Any ideas how to get the results of my query into SPSS?

Options:

• Export to Excel then import to SPSS... formatting things like dates become unwieldy
• Save query as a table in my database... but then I would have to make a new table every time I run the query, yes?
• As recommended below, simply run my SQL statement in the GET DATA statement of my SPSS syntax, but I am struggling with that...

UPDATE: In an attempt to use SPSS to run my SQL query I edited this code and get this error indicating that SPSS doesn't like my declaration of nvarchar (currently investigating how to handle this using alternative method). I have tested my connection between SPSS and SQL and the connection is good:

SQLExecDirect failed :[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'N'.

Here is my query simplified to pull just one field from one table:

GET DATA 
/TYPE=ODBC 
/CONNECT='DSN=temp_Hisp;Description=tempHisp;UID=;Trusted_Connection=Yes;APP=IBM SPSS '+ 
'Products: Statistics Common;WSID=ARCH5-50;DATABASE=temp_HispTreat' 
/SQL='With CTE_BASENG As (Select StudyID, Visit, Question, CAST(Response As Int) As RESPONSE from temp_HispTreat.dbo.BAS AS PVTable outer apply (values (N'BAS1',BAS1), +'
'(N'BAS24',BAS24)) P(Question, Response)) select SubVis.IRB#, SubVis.StudyID, SubVis.Clin_num, Subvis.Visit, BASENG.BAS_ENGTOT From (Select Distinct IRB#, StudyID, +'
'Clin_Num, Visit_ID As Visit from temp_HispTreat.dbo.Subjects, temp_HispTreat.dbo.StudyStructure where subjects.IRB# = 5516 and StudyStructure.IRB = 5516) As SubVis left join (Select StudyID, +'
'Visit, SUM (Scoring.dbo.GetValue9(response)) As BAS_ENGTOT from CTE_BASENG group by StudyID, Visit) AS BASENG On SubVis.Studyid = BASENG.StudyID And SubVis.Visit = BASENG.Visit'
/ASSUMEDSTRWIDTH=255. 
CACHE.
EXECUTE.

Thanks all: Solved. There is quite a bit of tweaking necessary to get SPSS to run SQL query, but this is the best way to export SQL data into SPSS. In my case (values (N'BAS1',BAS1) had to be changed to (values ("BAS1",BAS1) but all of my commands, e.g. outer apply, union, etc, ran like champs! Appreciate the help.

Answer 1

You can use GET DATA procedure to import data from SQL directly in SPSS. See the SQL subcommand. You can use your complicated query here. For example:

GET DATA
 /TYPE = ODBC
 /CONNECT = "DSN = DSNname"
 /SQL = "SELECT * FROM empl_data "
        "WHERE ((bdate>=#1/1/1960# and edate<=#12/31/1960#) or bdate is null)".

It is clear why (values (N'BAS1',BAS1) caused the error. Because you are using single quotes for the argument of the SQL subcommand \SQL = ' '. And the first single quote in (values (N'BAS1',BAS1) defines the end of the argument. Switching to double quotes solves it.

I tried to rearrange your code. I can not test it, but I believe it should work:

GET DATA
  /TYPE = ODBC 
  /CONNECT = "DSN=temp_Hisp;DATABASE=temp_HispTreat"
  /SQL = "With CTE_BASENG As (Select StudyID, Visit, Question, "
           "CAST(Response As Int) As RESPONSE "
         "from temp_HispTreat.dbo.BAS AS PVTable "
         "outer apply (values (N'BAS1',BAS1), (N'BAS24',BAS24)) "
           "P(Question, Response)) "
         "select SubVis.IRB#, SubVis.StudyID, SubVis.Clin_num, Subvis.Visit, "
           "BASENG.BAS_ENGTOT "
         "From (Select Distinct IRB#, StudyID, Clin_Num, Visit_ID As Visit "
         "from temp_HispTreat.dbo.Subjects, temp_HispTreat.dbo.StudyStructure "
         "where subjects.IRB# = 5516 and StudyStructure.IRB = 5516) As SubVis "
         "left join (Select StudyID, Visit, "
           "SUM(Scoring.dbo.GetValue9(response)) As BAS_ENGTOT "
         "from CTE_BASENG group by StudyID, Visit) AS BASENG On "
           "SubVis.Studyid = BASENG.StudyID And SubVis.Visit = BASENG.Visit".

Answer 2

The SQL is processed by the ODBC driver, so the capabilities of that driver will determine what sort of SQL can be issued. The capabilities may be database specific. Someetimes there are multiple drivers available for a particular database, some from the IBM SPSS Data Access Pack and some from a db vendor directly, so you may want to investigate what is available for your particular database.