I NEED HELP TO CONVERT A SIMPLE SQL QUERY TO SPSS SYNTAX

[ferrer_a]

I NEED HELP TO CONVERT A SIMPLE SQL QUERY TO SPSS SYNTAX

SELECT Paragon_TPM300_PAT_VISIT.vst_int_id, Paragon_TPM300_PAT_VISIT.vst_ext_id, Paragon_TPM300_PAT_VISIT.adm_ts, Paragon_TSM910_ICD9_REF.icd9_int_id, Paragon_TPM318_VISIT_DIAGNOSIS.ICD9_diag_ty
FROM (Paragon_TPM300_PAT_VISIT LEFT JOIN Paragon_TPM318_VISIT_DIAGNOSIS ON Paragon_TPM300_PAT_VISIT.vst_int_id = Paragon_TPM318_VISIT_DIAGNOSIS.vst_int_id) LEFT JOIN Paragon_TSM910_ICD9_REF ON Paragon_TPM318_VISIT_DIAGNOSIS.icd9_int_id = Paragon_TSM910_ICD9_REF.icd9_int_id
WHERE (((Paragon_TPM318_VISIT_DIAGNOSIS.ICD9_diag_ty)='P'));

[Andy W]

More specifics would be useful:

1) If you have access to the database, you can simply copy and paste the current SQL query into a GET DATA command. See here for an example, http://stackoverflow.com/a/22359289/604456

2) If you have the tables as separate SPSS data files, say 'Data1' and 'Data2', a LEFT JOIN of the type

FROM Data1
LEFT JOIN Data2
ON Data1.JoinID = Data2.JoinID

would be equivalent to in SPSS syntax

MATCH FILES FILE = 'Data1'
  /TABLE = 'Data2'
  /BY JoinID.

The WHERE clause can be replicated by a SELECT IF command. (Note in SPSS when using MATCH FILES all files need to be sorted in ascending order on the BY variables.)

3) There is also the STAR JOIN command, which you can use similar SQL syntax. I'm unsure if your subquery can be replicated in STAR JOIN offhand, but if it can't I imagine it could be with two separate STAR JOIN commands (ditto with MATCH FILES)

[ferrer_a]

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=AuxilioDatabases;UID=;Trusted_Connection=Yes;APP=IBM SPSS Products: Statistics '+
    'Common;WSID=DCDL80Y3L02'
  /SQL="SELECT T0.vst_int_id, T0.vst_ext_id, T0.adm_ts, T1.ICD9_diag_ty, T2.icd9_int_id FROM "+
    "AuxilioMutuo.Paragon.TPM300_PAT_VISIT T0, AuxilioMutuo.Paragon.TPM318_VISIT_DIAGNOSIS T1, "+
    "AuxilioMutuo.Paragon.TSM910_ICD9_REF T2 WHERE T0.vst_int_id = T1.vst_int_id AND "+
    "T1.icd9_int_id = T2.icd9_int_id AND (T1.ICD9_diag_ty = 'P')"
  /ASSUMEDSTRWIDTH=255.

CACHE.
EXECUTE.
DATASET NAME DataSet6 WINDOW=FRONT.

THIS IS THE QUERY IN SPSS ONLY THAT IS INNER JOIN AND I NEED TO MAKE IT LEFT OUTER JOIN (LEFT LEFT).  I'M USING GET DATA ALL THE TIME - THE PROBLEM IS HOW TO MAKE A SPSS SYNTAX (LEFT OUTER JOIN) WITH THREE TABLES.

[Andy W]

I don't understand your question. If you have a valid SQL statement then you can submit that to GET DATA.

The same advice applies even if that weren't the case. Just grab the separate data files  and then do the MATCH FILES on the successive tables.

[David Marso]

"THIS IS THE QUERY IN SPSS ONLY THAT IS INNER JOIN AND I NEED TO MAKE IT LEFT OUTER JOIN (LEFT LEFT).  I'M USING GET DATA ALL THE TIME - THE PROBLEM IS HOW TO MAKE A SPSS SYNTAX (LEFT OUTER JOIN) WITH THREE TABLES. "

No need to SHOUT!!!!
Read up on MATCH FILES!!!!!!!!!!!!!

ferrer_a wrote

GET DATA
  /TYPE=ODBC
  /CONNECT='DSN=AuxilioDatabases;UID=;Trusted_Connection=Yes;APP=IBM SPSS Products: Statistics '+
    'Common;WSID=DCDL80Y3L02'
  /SQL="SELECT T0.vst_int_id, T0.vst_ext_id, T0.adm_ts, T1.ICD9_diag_ty, T2.icd9_int_id FROM "+
    "AuxilioMutuo.Paragon.TPM300_PAT_VISIT T0, AuxilioMutuo.Paragon.TPM318_VISIT_DIAGNOSIS T1, "+
    "AuxilioMutuo.Paragon.TSM910_ICD9_REF T2 WHERE T0.vst_int_id = T1.vst_int_id AND "+
    "T1.icd9_int_id = T2.icd9_int_id AND (T1.ICD9_diag_ty = 'P')"
  /ASSUMEDSTRWIDTH=255.

CACHE.
EXECUTE.
DATASET NAME DataSet6 WINDOW=FRONT.

THIS IS THE QUERY IN SPSS ONLY THAT IS INNER JOIN AND I NEED TO MAKE IT LEFT OUTER JOIN (LEFT LEFT).  I'M USING GET DATA ALL THE TIME - THE PROBLEM IS HOW TO MAKE A SPSS SYNTAX (LEFT OUTER JOIN) WITH THREE TABLES.

[ferrer_a]

Sorry for that...

Thanks a lot for the help.

Alejandro

[Richard Ristow]

>This is the query in SPSS only that is inner join and I need to make
>it left outer join (Left Left). I'm using GET DATA all the time -
>the problem is how to make a SPSS syntax (left outer join) with three tables."

Your GET DATA command, including its SQL, is (reformatted),

>GET DATA
>   /TYPE=ODBC
>   /CONNECT='DSN=AuxilioDatabases;UID=;Trusted_Connection=Yes;' +
>            'APP=IBM SPSS Products: Statistics Common;'         +
>            'WSID=DCDL80Y3L02'
>   /SQL=    "SELECT  T0.vst_int_id,   T0.vst_ext_id,  T0.adm_ts, "     +
>                    "T1.ICD9_diag_ty, "                                +
>                    "T2.icd9_int_id "                                  +
>            "FROM    AuxilioMutuo.Paragon.TPM300_PAT_VISIT       T0, " +
>                    "AuxilioMutuo.Paragon.TPM318_VISIT_DIAGNOSIS T1, " +
>                    "AuxilioMutuo.Paragon.TSM910_ICD9_REF        T2  " +
>            "WHERE   T0.vst_int_id    = T1.vst_int_id  "               +
>               "AND  T1.icd9_int_id   = T2.icd9_int_id "               +
>               "AND (T1.ICD9_diag_ty = 'P')"
>   /ASSUMEDSTRWIDTH=255.

Is there a particular reason you don't do this by modifying the SQL?
I understand that it's quite easy to specify outer, or left-outer,
joins; I don't write SQL well enough to suggest the syntax.

But if you really want to do it at the SPSS level, you'd probably
start by reading your T1, T1, and T2 in separately as SPSS
datasets.   That would mean three GET DATA statements followed by
DATASET NAME statements, which I presume would look like this (below,
is for T0 only):

GET DATA
   /TYPE=ODBC
   /CONNECT='DSN=AuxilioDatabases;UID=;Trusted_Connection=Yes;' +
            'APP=IBM SPSS Products: Statistics Common;'         +
            'WSID=DCDL80Y3L02'
   /SQL=    "SELECT  T0.vst_int_id,   T0.vst_ext_id,  T0.adm_ts,    "  +
            "FROM    AuxilioMutuo.Paragon.TPM300_PAT_VISIT       T0 "
   /ASSUMEDSTRWIDTH=255.

DATASET NAME T0.

After that, the SPSS syntax depends on which keys are unique
identifiers in which files:
Is icd9_int_id a unique identifier in T1? in T2?
Is vst_int_id  a unique identifier in T0? in T1?

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD