CODEWITHSUNDEEP
spssspss-modeler
Karim Souidi
Karim Souidi

Reputation: 11

SPSS Restructure Data

I have data in the following format:

ID  Var1
1   a
1   a
1   b
1   b
2   c
2   c
2   c

I'd like to convert it (restructure it) to the following format in SPSS:

ID  Var1_1  Var1_2  Var1_3     Total_Count 
1   n(a)=2  n(b)=2  n( c )=0    4
2   n(a)=0  n(b)=0  n( c )=3    3

Upvotes: 1

Views: 329

Answers (3)

Julian
Julian

Reputation: 152

The transpose node comes in handy if you use version 18.1. As it is a simple pivot, you can go to "Fields and Records", then place the ID in "Index", Var1 in "Fields" and see if you can add another field for Count aggregation. If not, just derive it.

Upvotes: 0

Steve Gawtry
Steve Gawtry

Reputation: 11

If you're doing this in SPSS Modeler, here is a stream image that works for this. The order is:

  1. Create Data Set using User Input node, setting ID to integer and Var1 to string
  2. Restructure by Var1 values to generate field Var1_a, Var1_b, and Var1_c
  3. Aggregate using key field ID to sum counts Var1_a, Var1_b, and Var1_c, allowing Record Count field to be generated
  4. Output to Table

Restructure and Aggregate in SPSS Modeler

Upvotes: 1

eli-k
eli-k

Reputation: 11360

First I'll create some fake data to work with:

data list list/ID (f1)  Var1 (a1).
begin data
1   a
1   a
1   b
1   b
2   c
2   c
2   c
3   b
3   c
3   c
3   c
end data.
dataset name ex.

Now you can run the following - aggregate, restructure, create the string with the counts:

aggregate outfile=* /break ID Var1/n=n.
sort cases by ID Var1.
casestovars /id=ID /index=var1.
recode a b c (miss=0).
string Var1_1 Var1_2 Var1_3 (a10).
do repeat abc=a b c/Var123=Var1_1 Var1_2 Var1_3/val="a" "b" "c".
  compute Var123=concat("n(", val, ")=", ltrim(string(abc, f3))).
end repeat.
compute total_count=sum(a, b, c).

Upvotes: 1

Related Questions