Good day , I have a question. I am working on the data restructuring and have some difficulties to regroup cases to variables using index because of the same unique ID. Let me explain. Initial data set has 4 columns ( variables). Added new variable Sequence, sort cases by group syntax. Based on frequency stats, File 1 has  3 cases, File 2 has 2 case and File 3, 4 has 1 case each.

File	Classes	Category	Indicator	Sequence
1	Computer Science	Knowledge	Common Knowledge	1
1	Social Science	Communities	Locally Based Communities	2
1	Computer Science	Knowledge	Foundation Knowledge	3
2	Technology	Engineering	Civil Engineering	1
2	Computer Science	Data Processing	Automatic data processing	2
3	Social Science	Political Science	System of Governments	1
4	Language	Linguistics	Grammar	1

Where we have 6 categories and 7 indicators.
My goal is to regroup the cases into the variables by creating index variable for each value option within the current variable, so it could be transformed to

	Classes 4 max				Categories max 6
File	ComputerScience	Language	SocialScience	Technology	Communities	DataProcessing	Engineering	Knowledge	Linguistics	PoliticalScience	CasesTotal
1	Yes		Yes		Yes			Yes			3
2	Yes			Yes		Yes	Yes				2
3			Yes							Yes	1
4		Yes							Yes		1

As you could see proposed index 3 variables could have duplicates and I am not able to use basic regroup option from the system.
For example File 1 has 2 cases with computer science and 2 cases with knowledge category. However in the regrouping its listed once. However, the total cases shows 3.
Thank you in advance for any suggestion of reference to the knowledge articles.

------------------------------
Kamal K
------------------------------
#SPSSStatistics

Hi. I don't think CASESTOVARS is what you want for this. Here is my attempt at giving you what I think you want:

FILE HANDLE newData /NAME="<some writeable path>/newData.sav".

** I used the data in your question as a template.

dataset close all.

data list free /File (F1) Classes Category Indicator (3A32) Sequence (F1).
begin data.
1 ComputerScience Knowledge CommonKnowledge 1
1 SocialScience Communities LocallyBasedCommunities 2
1 ComputerScience Knowledge FoundationKnowledge 3
2 Technology Engineering CivilEngineering 1
2 ComputerScience DataProcessing AutomaticDataProcessing 2
3 SocialScience PoliticalScience SystemOfGovernments 1
4 Language Linguistics Grammar 1
end data.

SPSSINC CREATE DUMMIES VARIABLE=Classes Category Indicator
  ROOTNAME2=twoway ROOTNAME3=threeway
  /OPTIONS ORDER=A USEVALUELABELS=NO USEML=NO OMITFIRST=NO.

OMS /SELECT TABLES  /DESTINATION FORMAT=SAV OUTFILE=newData
  /IF SUBTYPES='Crosstabulation' /TAG="_x_".

MULT RESPONSE GROUPS=$twoway (twoway_1_1 twoway_1_2 twoway_1_3 twoway_1_4 twoway_1_5
  twoway_1_6 twoway_1_10 twoway_1_11 twoway_1_12 twoway_1_13 twoway_1_14 twoway_1_15
  twoway_1_16 twoway_1_17 twoway_1_18 twoway_1_19 twoway_1_20 (1))
  /VARIABLES=File(1 4)
  /TABLES=File BY $twoway
  /BASE=CASES.

OMSEND TAG="_x_".

GET FILE newData /DROP Command_ TO Label_ Var3.
SELECT IF Var1 NE "Total".
LIST.

** The newData file is what I'm guessing you wanted.

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

Thank you @Rick Marcantonio for your method. I was able to replicate it in real datasets. I was able to create the two and three way point, which I highly appreciate to learn from you. Also, I was able to use aggregate function and sum up the variables.
​

------------------------------
Kamal K
------------------------------

Original Message:
Sent: Tue January 10, 2023 09:48 AM
From: Rick Marcantonio
Subject: Data Restructure from cases to variables with index values

Hi. I don't think CASESTOVARS is what you want for this. Here is my attempt at giving you what I think you want:

FILE HANDLE newData /NAME="<some writeable path>/newData.sav".

** I used the data in your question as a template.

dataset close all.

data list free /File (F1) Classes Category Indicator (3A32) Sequence (F1).
begin data.
1 ComputerScience Knowledge CommonKnowledge 1
1 SocialScience Communities LocallyBasedCommunities 2
1 ComputerScience Knowledge FoundationKnowledge 3
2 Technology Engineering CivilEngineering 1
2 ComputerScience DataProcessing AutomaticDataProcessing 2
3 SocialScience PoliticalScience SystemOfGovernments 1
4 Language Linguistics Grammar 1
end data.

SPSSINC CREATE DUMMIES VARIABLE=Classes Category Indicator
  ROOTNAME2=twoway ROOTNAME3=threeway
  /OPTIONS ORDER=A USEVALUELABELS=NO USEML=NO OMITFIRST=NO.

OMS /SELECT TABLES  /DESTINATION FORMAT=SAV OUTFILE=newData
  /IF SUBTYPES='Crosstabulation' /TAG="_x_".

MULT RESPONSE GROUPS=$twoway (twoway_1_1 twoway_1_2 twoway_1_3 twoway_1_4 twoway_1_5
  twoway_1_6 twoway_1_10 twoway_1_11 twoway_1_12 twoway_1_13 twoway_1_14 twoway_1_15
  twoway_1_16 twoway_1_17 twoway_1_18 twoway_1_19 twoway_1_20 (1))
  /VARIABLES=File(1 4)
  /TABLES=File BY $twoway
  /BASE=CASES.

OMSEND TAG="_x_".

GET FILE newData /DROP Command_ TO Label_ Var3.
SELECT IF Var1 NE "Total".
LIST.

** The newData file is what I'm guessing you wanted.

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------