SYNTAX help needed

[Jennifer S. Randolph]

Hello, I am a relatively new SPSS user, with previous SAS experience, and have a syntax question.

I need to update an existing database periodically with new data that provides ratings on each of two versions of a task administered to subjects.  The main database is organized with variables that designate the the two versions of the taks as "a" and "b", and all associated varaibles are so designated (and the order of administration is critical information-- a was the version administered first, b second).  However, the transactional data I need to update the main database with is not organized similarly- there is a case entry for each run of th task.

so, main database:

ID      VER_A   SCORE_A VER_B   SCORE_B
100     1       .8              3       1
101     2       0               4       .5
102     4       1               1       .5
103     3                       2
104     1                       2
105     4                       3

 Note that IDs 103-105 need their score data, but they do have their version data.

and, transaction database:

ID      VER     SCORE
103     2       1
103     3       .5
104     1       1
104     2       1
105     3       .5
105     4       0

Is there some way to put an IF, THEN in the update command? ..IF ver=ver_A then score=score_a, or something to that effect?  any help will be much appreciated.

Jennifer Stone Randolph, M.Sc.
Brain Imaging Laboratory
Department of Psychiatry

Confidentiality Notice: This e-mail transmission is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution violates confidentiality and privacy laws and is prohibited. If you are not the intended recipient, please contact the sender immediately and destroy all copies of the message. Thank you for your cooperation.

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

[Maguin, Eugene]

Jennifer,

I think you have to run two passes using the Update command. The first pass
is

Update file=main/file=tranaction/rename=(ver=ver_a)/by id ver_a.

Pass 2 is

Update file=main/file=tranaction/rename=(ver=ver_b)/by id ver_b.


I've never had to do this sort of operation but this is where I'd start.

Gene Maguin


>>I need to update an existing database periodically with new data that
provides ratings on each of two versions of a task administered to subjects.
The main database is organized with variables that designate the the two
versions of the taks as "a" and "b", and all associated varaibles are so
designated (and the order of administration is critical information-- a was
the version administered first, b second).  However, the transactional data
I need to update the main database with is not organized similarly- there is
a case entry for each run of th task.

so, main database:

ID      VER_A   SCORE_A VER_B   SCORE_B
100     1       .8              3       1
101     2       0               4       .5
102     4       1               1       .5
103     3                       2
104     1                       2
105     4                       3

 Note that IDs 103-105 need their score data, but they do have their version
data.

and, transaction database:

ID      VER     SCORE
103     2       1
103     3       .5
104     1       1
104     2       1
105     3       .5
105     4       0

Is there some way to put an IF, THEN in the update command? ..IF ver=ver_A
then score=score_a, or something to that effect?  any help will be much
appreciated.

Jennifer Stone Randolph, M.Sc.
Brain Imaging Laboratory
Department of Psychiatry

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

[Bruce Weaver]

Jennifer S. Randolph wrote

Hello, I am a relatively new SPSS user, with previous SAS experience, and have a syntax question.

I need to update an existing database periodically with new data that provides ratings on each of two versions of a task administered to subjects.  The main database is organized with variables that designate the the two versions of the taks as "a" and "b", and all associated varaibles are so designated (and the order of administration is critical information-- a was the version administered first, b second).  However, the transactional data I need to update the main database with is not organized similarly- there is a case entry for each run of th task.

so, main database:

ID      VER_A   SCORE_A VER_B   SCORE_B
100     1       .8              3       1
101     2       0               4       .5
102     4       1               1       .5
103     3                       2
104     1                       2
105     4                       3

 Note that IDs 103-105 need their score data, but they do have their version data.

and, transaction database:

ID      VER     SCORE
103     2       1
103     3       .5
104     1       1
104     2       1
105     3       .5
105     4       0

Is there some way to put an IF, THEN in the update command? ..IF ver=ver_A then score=score_a, or something to that effect?  any help will be much appreciated.

Jennifer Stone Randolph, M.Sc.
Brain Imaging Laboratory
Department of Psychiatry

This is not terribly elegant, but I think it does what you want.

new file.
dataset close all.

* Create the two sample datasets.

data list list / ID(f5.0) VER_A (f2.0)  SCORE_A (f3.1) VER_B (f2.0)  SCORE_B (f3.1).
begin data
100     1       .8              3       1
101     2       0               4       .5
102     4       1               1       .5
103     3       .               2       .
104     1       .               2       .
105     4       .               3       .
end data.

dataset name main.

data list list / ID (f5.0)  VER (f2.0)  SCORE (f3.1).
begin data
103     2       1
103     3       .5
104     1       1
104     2       1
105     3       .5
105     4       0
end data.
dataset name transact.

* Number records within ID in the transaction dataset .

dataset activate transact.
compute case = $casenum.
RANK VARIABLES=case (A) BY id
  /RANK
  /PRINT=YES
  /TIES=MEAN.
rename var(rcase = recnum).
format recnum(f2.0).

* Move all transaction data to a single row.

dataset activate transact.
CASESTOVARS
  /ID=ID
  /INDEX=recnum
  /DROP = case
  /GROUPBY=INDEX.

* Merge the two datasets .

dataset activate main.
match files
 file = * /
 file = 'transact' /
 by ID .
exe.

* Fill in the missing scores .

if (ver.1 = ver_a) score_a = score.1 .
if (ver.1 = ver_b) score_b = score.1 .
if (ver.2 = ver_a) score_a = score.2 .
if (ver.2 = ver_b) score_b = score.2 .

list  ID to SCORE_B .

* Delete the no longer needed variables (if you wish) .

DELETE VARIABLES  VER.1 to SCORE.2 .

[Melissa Ives]

Couldn't you make the main dataset tall (VARSTOCASES), then update with the transaction file (/BY ID Ver) and reconvert to wide (CASESTOVARS)?

Remember UPDATE adds variable values, variables and cases from the transaction file - even if they are not in the main file--use MATCH FILES/TABLE= to match without adding cases.  The value is taken from the first file listed, so if you list the transaction file first those are the values that will be kept.

HTH
Melissa
-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Jennifer S. Randolph
Sent: Monday, December 14, 2009 9:48 AM
To: [hidden email]
Subject: [SPSSX-L] SYNTAX help needed

Hello, I am a relatively new SPSS user, with previous SAS experience, and have a syntax question.

I need to update an existing database periodically with new data that provides ratings on each of two versions of a task administered to subjects.  The main database is organized with variables that designate the the two versions of the taks as "a" and "b", and all associated varaibles are so designated (and the order of administration is critical information-- a was the version administered first, b second).  However, the transactional data I need to update the main database with is not organized similarly- there is a case entry for each run of th task.

so, main database:

ID      VER_A   SCORE_A VER_B   SCORE_B
100     1       .8              3       1
101     2       0               4       .5
102     4       1               1       .5
103     3                       2
104     1                       2
105     4                       3

 Note that IDs 103-105 need their score data, but they do have their version data.

and, transaction database:

ID      VER     SCORE
103     2       1
103     3       .5
104     1       1
104     2       1
105     3       .5
105     4       0

Is there some way to put an IF, THEN in the update command? ..IF ver=ver_A then score=score_a, or something to that effect?  any help will be much appreciated.

Jennifer Stone Randolph, M.Sc.
Brain Imaging Laboratory
Department of Psychiatry

Confidentiality Notice: This e-mail transmission is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution violates confidentiality and privacy laws and is prohibited. If you are not the intended recipient, please contact the sender immediately and destroy all copies of the message. Thank you for your cooperation.

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

PRIVILEGED AND CONFIDENTIAL INFORMATION
This transmittal and any attachments may contain PRIVILEGED AND
CONFIDENTIAL information and is intended only for the use of the
addressee. If you are not the designated recipient, or an employee
or agent authorized to deliver such transmittals to the designated
recipient, you are hereby notified that any dissemination,
copying or publication of this transmittal is strictly prohibited. If
you have received this transmittal in error, please notify us
immediately by replying to the sender and delete this copy from your
system. You may also call us at [phone] for assistance.

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