CASETOVARS / Restructure - Create Combinations where no values exist

[ariel barak]

Hi All,

I'm guessing this has been covered already, but I haven't been able to find it in the archives.

I'm restructuring some data after aggregating it and am wondering if there is a subcommand in CASETOVARS or other syntax that will generate rows for all combinations of two fields.

The variables are name, month, Description, Result, and Count. I would like to restructure so that for all of the Name/Description/Result combinations are created even if all values are system missing. The field titled Description is a string, of which there are two options (Supervised and Unsupervised) - this could be switched to a numeric variable if that makes things easier. The second field is a numeric field and is titled Result, of which there are 3 options with a label (1,2,999 = Occurred, Did Not Occur, Blank).

Here is an example of the initial data.

DATA LIST LIST /name (A30) Month (F11.0) Description (A40) Result (F8.0) Count (F8.0).

BEGIN DATA

Anderson 1 Supervised 1 16

Anderson 1 Supervised 2 1

Anderson 1 Supervised 999 1

Anderson 2 Supervised 1 18

Anderson 2 Supervised 2 5

Anderson 2 Unsupervised 1 2

Brady 1 Supervised 1 25

Brady 1 Supervised 2 4

Brady 1 Unsupervised 2 4

Brady 2 Supervised 1 20

Brady 2 Supervised 2 3

Brady 3 Supervised 1 19

Brady 3 Supervised 2 3

Brady 3 Supervised 999 2

Brady 4 Supervised 1 17

Brady 4 Supervised 2 5

END DATA.

DATASET NAME Initial.

DATASET ACTIVATE Initial.

VALUE LABELS Result 1 "Occurred" 2 "Did Not Occur" 999 "Blank".

I would like to get:

DATA LIST LIST /name (A30) Description (A40) Result (F8.0) Count.1 (F8.0) Count.2 (F8.0) Count.3 (F8.0) Count.4 (F8.0).

BEGIN DATA

Anderson Supervised  1 16 18 0 0

Anderson Supervised  2  1 5 0 0

Anderson Supervised  999 1 0 0 0

Anderson Unsupervised  1 0 2 0 0

Anderson Unsupervised  2 0 0 0 0

Anderson Unsupervised  999 0 0 0 0

Brady Supervised  1 25 20 19 17

Brady Supervised  2 4 3 3 5

Brady Supervised  999 0 0 2 0

Brady Unsupervised  1 0 0 0 0

Brady Unsupervised  2 4 0 0 0

Brady Unsupervised  999 0 0 0 0

END DATA.

DATASET NAME DesiredResult.

DATASET ACTIVATE DesiredResult.

VALUE LABELS Result 1 "Occurred" 2 "Did Not Occur" 999 "Blank".

I'm currently using:

 

SORT CASES BY Name Description Result Month.

CASESTOVARS

  /ID=Name Description Result 

  /INDEX=Month

  /GROUPBY=INDEX.

This get's me most of what I want, except that it doesn't generate the cases where all values would be system missing i.e. two rows for Anderson (Unsupervised Did Not Occur and Unsupervised Blank) and the two rows for Brady (Unsupervised Occurred and Unsupervised Blank). The number of months will vary from 1 to all 12 with potential gaps and names may be added each month that I run this syntax, so I'm looking for a somewhat flexible solution.

Thanks in advance,

Ariel

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

[Andy W]

I would create a second set of the expanded potential cases, and then MATCH FILES those two sets together. Example below with making the expanded set of names and description/result pairs via AGGREGATE and VARSTOCASES.

***********************************************.
DATA LIST LIST /name (A30) Month (F11.0) Description (A40) Result (F8.0) Count (F8.0).
BEGIN DATA
Anderson 1 Supervised 1 16
Anderson 1 Supervised 2 1
Anderson 1 Supervised 999 1
Anderson 2 Supervised 1 18
Anderson 2 Supervised 2 5
Anderson 2 Unsupervised 1 2
Brady 1 Supervised 1 25
Brady 1 Supervised 2 4
Brady 1 Unsupervised 2 4
Brady 2 Supervised 1 20
Brady 2 Supervised 2 3
Brady 3 Supervised 1 19
Brady 3 Supervised 2 3
Brady 3 Supervised 999 2
Brady 4 Supervised 1 17
Brady 4 Supervised 2 5
END DATA.
DATASET NAME Initial.
DATASET ACTIVATE Initial.
VALUE LABELS Result 1 "Occurred" 2 "Did Not Occur" 999 "Blank".

*Create an aggregate file of just the names.
DATASET DECLARE FullNames.
AGGREGATE OUTFILE='FullNames'
  /BREAK name
  /Total = N.
DATASET ACTIVATE FullNames.
*Now expand this set to Super/Unsuper and Result sets.
STRING D1 D2 (A40).
COMPUTE D1 = "Supervised".
COMPUTE D2 = "Unsupervised".
VARSTOCASES /MAKE Description FROM D1 D2 /DROP Total.
COMPUTE R1 = 1.
COMPUTE R2 = 2.
COMPUTE R3 = 999.
VARSTOCASES /MAKE Result FROM R1 TO R3.

*Now add in the Counts.
DATASET ACTIVATE Initial.
SORT CASES BY Name Description Result Month.
CASESTOVARS
  /ID=Name Description Result
  /INDEX=Month
  /GROUPBY=INDEX.
MATCH FILES FILE = *
  /FILE = 'FullNames'
  /BY Name Description Result.
***********************************************.

[ariel barak]

Hi Andy,

Perfect...this does the trick! 

I think completing this task via a subcommand or checkbox in the Restructure Wizard  would be helpful functionality for other SPSS users who would like their restructured data to have an identical structure for each set of cases, in my example, the field name.

Thanks again!

-Ariel

On Fri, Aug 28, 2015 at 11:02 AM, Andy W <[hidden email]> wrote:

I would create a second set of the expanded potential cases, and then MATCH
FILES those two sets together. Example below with making the expanded set of
names and description/result pairs via AGGREGATE and VARSTOCASES.

***********************************************.
DATA LIST LIST /name (A30) Month (F11.0) Description (A40) Result (F8.0)
Count (F8.0).
BEGIN DATA
Anderson 1 Supervised 1 16
Anderson 1 Supervised 2 1
Anderson 1 Supervised 999 1
Anderson 2 Supervised 1 18
Anderson 2 Supervised 2 5
Anderson 2 Unsupervised 1 2
Brady 1 Supervised 1 25
Brady 1 Supervised 2 4
Brady 1 Unsupervised 2 4
Brady 2 Supervised 1 20
Brady 2 Supervised 2 3
Brady 3 Supervised 1 19
Brady 3 Supervised 2 3
Brady 3 Supervised 999 2
Brady 4 Supervised 1 17
Brady 4 Supervised 2 5
END DATA.
DATASET NAME Initial.
DATASET ACTIVATE Initial.
VALUE LABELS Result 1 "Occurred" 2 "Did Not Occur" 999 "Blank".

*Create an aggregate file of just the names.
DATASET DECLARE FullNames.
AGGREGATE OUTFILE='FullNames'
  /BREAK name
  /Total = N.
DATASET ACTIVATE FullNames.
*Now expand this set to Super/Unsuper and Result sets.
STRING D1 D2 (A40).
COMPUTE D1 = "Supervised".
COMPUTE D2 = "Unsupervised".
VARSTOCASES /MAKE Description FROM D1 D2 /DROP Total.
COMPUTE R1 = 1.
COMPUTE R2 = 2.
COMPUTE R3 = 999.
VARSTOCASES /MAKE Result FROM R1 TO R3.

*Now add in the Counts.
DATASET ACTIVATE Initial.
SORT CASES BY Name Description Result Month.
CASESTOVARS
  /ID=Name Description Result
  /INDEX=Month
  /GROUPBY=INDEX.
MATCH FILES FILE = *
  /FILE = 'FullNames'
  /BY Name Description Result.
***********************************************.



-----
Andy W
[hidden email]
http://andrewpwheeler.wordpress.com/
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/CASETOVARS-Restructure-Create-Combinations-where-no-values-exist-tp5730538p5730540.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

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

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