odbc SQL one more time

[Raffe, Sydelle, SSA]

I must be getting closer and now understand (I think) the use of single quotes and lines in the script.

However, I'm still getting an error message as below:

GET DATA /TYPE=ODBC /CONNECT=
 'DSN=CIS01USER;UID=CIS01USER/CIS01USER;PWD=;DBQ=CIS01USER ;DBA=W;APA=T;EXC'+
 '=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;'
  'BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;'
 /SQL = 'SELECT T5568.CWIN,  T5568.EFF_BGN_DT,  T5568.EFF_END_DT,  T5568.E'+
 'MP_STS_CD,  T5568.HIST_IND,  T4874.PGM_TYP_CD,  '
  'T4874.CS_ID,  T4874.CWIN AS CWIN1,  T4874.ELIG_STS_CD,  T5187.CSLD_ID,  '+
 'T5783.OFF_ID,  T5783.CSLD_ID AS CSLD_ID1,  '
  'T5187.CS_ID AS CS_ID1,  T7092.OFF_NM,  T7092.OFF_ID AS OFF_ID1 FROM  CIS'+
 '01.INDV_WRK_REQR T5568, CIS01.AG_INDV_ELIG_RSLT '
  'T4874, CIS01.CS_PGM T5187, CIS01.SE_CSLD T5783, CIS01.RT_OFF T7092 '
' WHERE '
 ' T4874.CWIN = T5568.CWIN '
' and T5187.CS_ID =  T4874.CS_ID '
'  and T5783.CSLD_ID = T5187.CSLD_ID '
'  and T7092.OFF_ID = T5783.OFF_ID '
' and T5568.CWIN = T4874.CWIN '
     '  AND T4874.CS_ID = T5187.CS_ID '
     '  AND T5187.CSLD_ID = T5783.CSLD_ID '
     '  AND T5783.OFF_ID = T7092.OFF_ID  '
' and t4874.PGM_typ_cd = CW CR WW '
 ' AND (YEAR(T5568.EFF_BGN_DT) ) GE 2005 '
' AND T5568.EFF_END_DT NE NULL  ; '
 /ASSUMEDSTRWIDTH=255 .

>Warning.  Command name: GET DATA
>SQLExecDirect failed :[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

CACHE.  //May be removed but insures data integrity.



Sydelle Raffe, Alameda County Social Services Agency
Information Services Division, Office of Data Management
e:mail:  [hidden email]
phone: [phone]     fax: [phone]
If you have a request for information, please submit an ODM request form at:  https://alamedasocialservices.org/staff/support_services/statistics_and_reports/odm/index.cfm

====================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

[stace swayne]

Dear list,

  I'm working with a data set that has duplicate ID's and I would like to delete the duplicates, and so far I have been doing it manually, but I wanted to know if there is a way to do this with syntax.

  All suggestions are appreciated,

  thank you,

  Stace


---------------------------------
Never miss a thing.   Make Yahoo your homepage.

=====================
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

[David Futrell]

You can do this through the "Data Menu," at least in Version 15.0. You can paste the syntax from there.

Here are a few other, more elegant methods you can use in syntax. I can't take credit for these...I think I swiped them from Raynald's site.

* (best method) to delete duplicate records in a file (identical records).
 SORT CASES BY ALL.
AGGREGATE OUTFILE=*
/PRESORTED
/BREAK=ALL
 /N=n.

* The above syntax is capable to handle up to 4,000 variables.

* (alternative method).

SORT CASES BY all.
MATCH FILES FILE=*
/BY all
/FIRST= first.
SELECT IF first.

* To delete records with identical V1 values.
SORT CASES BY v1.
MATCH FILES FILE=*
/BY v1
/FIRST = first .
SELECT IF first.

* To delete records with identical var1, var2 and var3.
SORT CASES BY var1 var2 var3.
ADD FILES FILE=*
/BY var1 var2 var3
/FIRST=first.
SELECT IF first.


stace swayne <[hidden email]> wrote: Dear list,

  I'm working with a data set that has duplicate ID's and I would like to delete the duplicates, and so far I have been doing it manually, but I wanted to know if there is a way to do this with syntax.

  All suggestions are appreciated,

  thank you,

  Stace


---------------------------------
Never miss a thing.   Make Yahoo your homepage.

=====================
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



---------------------------------
Looking for last minute shopping deals?  Find them fast with Yahoo! Search.

=====================
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

[vlad simion]

Hi Stace,

look at the Identify Duplicate Cases in the Data menu.
It looks something like this:

SORT CASES BY Project_ISIS(A) .
MATCH FILES /FILE = * /BY Project_ISIS
 /FIRST = PrimaryFirst /LAST = PrimaryLast.
DO IF (PrimaryFirst).
COMPUTE MatchSequence = 1 - PrimaryLast.
ELSE.
COMPUTE MatchSequence = MatchSequence + 1.
END IF.
LEAVE MatchSequence.
FORMAT MatchSequence (f7).
COMPUTE InDupGrp = MatchSequence > 0.
SORT CASES InDupGrp(D).
MATCH FILES /FILE = * /DROP = PrimaryFirst InDupGrp MatchSequence.
VARIABLE LABELS PrimaryLast 'Indicator of each last matching case as
Primary' .
VALUE LABELS PrimaryLast 0 'Duplicate Case' 1 'Primary Case'.
VARIABLE LEVEL PrimaryLast (ORDINAL).
FREQUENCIES VARIABLES = PrimaryLast .
EXECUTE.

here it finds duplicate for the Project_ISIS variable.

hth,
Vlad

On Dec 13, 2007 8:58 PM, stace swayne <[hidden email]> wrote:

> Dear list,
>
>  I'm working with a data set that has duplicate ID's and I would like to
> delete the duplicates, and so far I have been doing it manually, but I
> wanted to know if there is a way to do this with syntax.
>
>  All suggestions are appreciated,
>
>  thank you,
>
>  Stace
>
>
> ---------------------------------
> Never miss a thing.   Make Yahoo your homepage.
>
> =====================
> 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
>

--
Vlad Simion
Data Analyst
Tel:      [phone]

=====================
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