Search code examples
spss

Table transformation from variables

My dataset looks like down below, but there are way more brands and categories.

enter image description here

I would like to transfer it the way that the brand is a row, and attribute (good quality, affordable) in the column

enter image description here

I've tried VARSTOCASES and i can calculate mean from it but thats not my desirable output

I need to posses brand names somehow - should withdraw it from all of my variables by

compute brand=char.substr(brand, 16)

like

compute brand=char.substr(P1_Good_Quality_BMW, 16)

I am fine with the varstocases part, then I can put my output like GQ to column, but dont know how to possess all of the names of brands and to let them match mean values of attributes

Thank you in advance for your help

Solution

  • This will get the data in the structure you intended - with a row for each brand and a column for each attribute:

    varstocases /make GQ from P1_GoodQuality_BMW P1_GoodQuality_Audi P1_GoodQuality_Mercedes
        /make Afford from P2_Affordable_BMW  P2_Affordable_Audi  P2_Affordable_Mercedes
       /index=brand(GQ).
* at this point you should have the table you were trying to create, 
* we'll just extract the brand names properly.
compute brand=char.substr(brand, 16).
execute.
* now we have the data structured nicely, we can aggregate by brand.
dataset declare agg.
aggregate /out=agg /break=brand /GQ Afford = mean (GQ Afford).
dataset activate agg.