SPSS Statistics

 View Only
  • 1.  Data Restructure from cases to variables with index values

    Posted Mon January 09, 2023 04:45 PM
    Edited by System Fri January 20, 2023 04:20 PM
    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


  • 2.  RE: Data Restructure from cases to variables with index values

    Posted Tue January 10, 2023 09:49 AM
    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
    ------------------------------



  • 3.  RE: Data Restructure from cases to variables with index values

    Posted Wed January 11, 2023 05:55 PM
    Edited by System Fri January 20, 2023 04:14 PM
    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
    ------------------------------