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.

Solution 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".

Solution 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.

Comments

[TSS]

Thanks @djhurio, but my qry starts off with WITH a statement that SPSS doesn't seem to recognize. WITH <alias_name> AS (sql_subquery_statement) SELECT column_list FROM <alias_name>[,tablename] [WHERE <join_condition>]

[djhurio]

I agree with @JKP. It is not a problem of SPSS. It should be related with ODBC data driver you are using. Do you get an error message?

[djhurio]

Check if your SQL statement is translated correctly. Take a special care about blanks at the end or beginning of each line. See the help pic.dhe.ibm.com/infocenter/spssstat/v20r0m0/topic/…

[TSS]

The error message it get is Warning. Command name: GET DATA >SQLExecDirect failed :[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'N'. But I can't for the life of me figure out which N is the issue. I can connect to tables and views using the ODBC code with no problem. I can post my SQL query code, augmented for SPSS. DO I do that here or edit my original question?

[djhurio]

Please add the SPSS code with the GET DATA statement by editing your question.

[djhurio]

General suggestion. Start with the most simplest code that works. Make the code more complex by adding commands in step-wise manner. It will be possible to detect which command triggers the warning or error.

[TSS]

Thanks, @JKP. Maybe this is an issue. I am using an institutional license of SPSS and attempts to go the IBM SPSS website to download more drivers are thwarted by the fact that I cannot obtain all the licensing. If I cannot call my query in SPSS, any other options?

[TSS]

Thanks @djhurio, I was being lazy. Seems, as I have edited above, that SPSS does not like my nvarchar declaration. I have voted up your answer as it is the best solution, I just need to keep struggling with getting my query to work in SPSS syntax statement.

[TSS]

Code works perfectly. Tweaking the phrasing for SPSS to read the code is exhausting, but I had success. Thanks!