IBM Support

How to count distinct values in a variable and compute a variable with frequency for distincts per ID?

Question & Answer


Question

I am working with PASW Statistics and have one ID variable with multiple rows per person and a string variable with categories. I would like to compute a variable which includes the frequencies per distinct value of every unique ID, can this be done with Statistics? Example: ID: V1: 1 a 1 a 1 b 2 a 2 b 2 b 2 c In this example ID 1 should get the frequency value 2 in a new variable since in V1 are two categories for this ID and for ID 2 the value of 3 should be displayed in the new variable.

Answer

This can be done with Syntax. In the syntax example below the original string variable is autorecoded into a numeric variable. After that, we create a new file with CASETOVARS so that we have a single row per ID. After that you can use the macro to compute the distinct variable:

DATA LIST LIST /id (F) v1 (A).
BEGIN DATA
1 a
1 a
1 b
2 a
2 b
2 b
2 c
END DATA.
LIST.

SAVE OUTFILE = 'Original.sav'.

AUTORECODE VARIABLES=v1
/INTO vnum
/PRINT.
DELETE VARIABLES v1.
SORT CASES BY id .
CASESTOVARS
/ID=id
/GROUPBY=VARIABLE.


*///////////////.
DEFINE !nb(nbvars=!TOKENS(1) /v1=!TOKENS(1) /v2=!TOKENS(1))

VECTOR v=!v1 TO !v2 /val(!nbvars).
LOOP #cnt=1 TO !nbvars.
COMPUTE val(#cnt)=v(#cnt).
END LOOP.

LOOP #cnt1 = 1 TO !nbvars - 1.
DO IF ~MISSING(val(#cnt1)).
LOOP #cnt2 = #cnt1 + 1 TO !nbvars.
IF val(#cnt2)=v(#cnt1) val(#cnt2)=$SYSMIS.
END LOOP.
END IF.
END LOOP.

COMPUTE distinct=!nbvars - NMISS(val1 TO !CONCAT('val',!nbvars)).
* Clean up.
ADD FILES FILE=* /DROP=val1 TO !CONCAT('val',!nbvars).

!ENDDEFINE.
*///////////////.

* Call macro.
SET MPRINT=yes.
!nb nbvars=4 v1=vnum.1 v2=vnum.4.
SET MPRINT=no.
EXECUTE.

SAVE OUTFILE='Result.sav'
Result:

ID: vnum.1: vnum.2: vnum.3: vnum.4: Distinct:
1 1 1 2 . 2
2 1 2 2 3

[{"Product":{"code":"SSLVMB","label":"SPSS Statistics"},"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Component":"Not Applicable","Platform":[{"code":"PF016","label":"Linux"},{"code":"PF014","label":"iOS"},{"code":"PF033","label":"Windows"}],"Version":"Not Applicable","Edition":""}]

Historical Number

83661

Document Information

Modified date:
16 June 2018

UID

swg21476228