My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

I am not sure I understand the setup, but it seems as if a sequence of AGGREGATE and restructuring the data from a long format to a wide one would work. In the syntax below, you can see that there is an addition of "VIND". In the menus for Data/Restructure/Cases into variables, that is achieved by ticking the "Create indicator variables" box.

DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME dataset.

*Here the number of instances for each combination of ind_id and PS is created with Data/Aggregate.

DATASET ACTIVATE dataset.
DATASET DECLARE aggr.
SORT CASES BY ind_id PS.
AGGREGATE
    /OUTFILE='aggr'
    /PRESORTED
    /BREAK=ind_id PS
    /N_BREAK=N.

*The numbers are not really needed, so that variable is deleted.

DATASET ACTIVATE aggr.
DELETE VARIABLES N_BREAK.

*A new dataset is set up. Not needed, but it could help to show what is happening.

DATASET ACTIVATE aggr.
DATASET COPY aggr1.
DATASET ACTIVATE aggr1.

*The dataset is restructured with the option that indicator variables are created.

SORT CASES BY ind_id PS.
CASESTOVARS
  /ID=ind_id 
  /INDEX=PS
  /GROUPBY=VARIABLE
  /VIND ROOT=ps.

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

I am not sure I understand the setup, but it seems as if a sequence of AGGREGATE and restructuring the data from a long format to a wide one would work. In the syntax below, you can see that there is an addition of "VIND". In the menus for Data/Restructure/Cases into variables, that is achieved by ticking the "Create indicator variables" box.

DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME dataset.

*Here the number of instances for each combination of ind_id and PS is created with Data/Aggregate.

DATASET ACTIVATE dataset.
DATASET DECLARE aggr.
SORT CASES BY ind_id PS.
AGGREGATE
    /OUTFILE='aggr'
    /PRESORTED
    /BREAK=ind_id PS
    /N_BREAK=N.

*The numbers are not really needed, so that variable is deleted.

DATASET ACTIVATE aggr.
DELETE VARIABLES N_BREAK.

*A new dataset is set up. Not needed, but it could help to show what is happening.

DATASET ACTIVATE aggr.
DATASET COPY aggr1.
DATASET ACTIVATE aggr1.

*The dataset is restructured with the option that indicator variables are created.

SORT CASES BY ind_id PS.
CASESTOVARS
  /ID=ind_id 
  /INDEX=PS
  /GROUPBY=VARIABLE
  /VIND ROOT=ps.

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

I am not sure I understand the setup, but it seems as if a sequence of AGGREGATE and restructuring the data from a long format to a wide one would work. In the syntax below, you can see that there is an addition of "VIND". In the menus for Data/Restructure/Cases into variables, that is achieved by ticking the "Create indicator variables" box.

DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME dataset.

*Here the number of instances for each combination of ind_id and PS is created with Data/Aggregate.

DATASET ACTIVATE dataset.
DATASET DECLARE aggr.
SORT CASES BY ind_id PS.
AGGREGATE
    /OUTFILE='aggr'
    /PRESORTED
    /BREAK=ind_id PS
    /N_BREAK=N.

*The numbers are not really needed, so that variable is deleted.

DATASET ACTIVATE aggr.
DELETE VARIABLES N_BREAK.

*A new dataset is set up. Not needed, but it could help to show what is happening.

DATASET ACTIVATE aggr.
DATASET COPY aggr1.
DATASET ACTIVATE aggr1.

*The dataset is restructured with the option that indicator variables are created.

SORT CASES BY ind_id PS.
CASESTOVARS
  /ID=ind_id 
  /INDEX=PS
  /GROUPBY=VARIABLE
  /VIND ROOT=ps.

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

Hello Lyndon.  If CROSSTABS gives you what you want, or nearly so, why not use OMS with CROSSTABS?  Here's an example using the small dataset Robert created in post #2 of the thread. 

* Use Robert's sample dataset.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME raw.

* Use OMS to write crosstabulation to a new dataset called xtab.
DATASET DECLARE  xtab.
OMS
  /SELECT TABLES
  /IF COMMANDS=['Crosstabs'] SUBTYPES=['Crosstabulation']
  /DESTINATION FORMAT=SAV NUMBERED=TableNumber_
   OUTFILE='xtab' VIEWER=YES
  /TAG = "xtab".
CROSSTABS ind_id BY PS.
OMSEND TAG = "xtab".

* Activate dataset xtab & do some clean-up.
DATASET ACTIVATE xtab.
SELECT IF Var1 NE "Total".
EXECUTE.
DELETE VARIABLES TableNumber_ TO Var1 Var3 Total.
RENAME VARIABLES (Var2 @1 @2 @3 = ind_id PS1 PS2 PS3).
RECODE PS1 TO PS3 (1 THRU HIGHEST = 1).
VARIABLE WIDTH ind_id (8).
LIST.

Output from the LIST command:

ind_id PS1 PS2 PS3 
 
1       1   1   1 
2       0   1   1 
3       1   0   0 
4       0   0   1 
5       1   0   1

Number of cases read:  5    Number of cases listed:  5

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

Hello Lyndon.  If CROSSTABS gives you what you want, or nearly so, why not use OMS with CROSSTABS?  Here's an example using the small dataset Robert created in post #2 of the thread. 

* Use Robert's sample dataset.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME raw.

* Use OMS to write crosstabulation to a new dataset called xtab.
DATASET DECLARE  xtab.
OMS
  /SELECT TABLES
  /IF COMMANDS=['Crosstabs'] SUBTYPES=['Crosstabulation']
  /DESTINATION FORMAT=SAV NUMBERED=TableNumber_
   OUTFILE='xtab' VIEWER=YES
  /TAG = "xtab".
CROSSTABS ind_id BY PS.
OMSEND TAG = "xtab".

* Activate dataset xtab & do some clean-up.
DATASET ACTIVATE xtab.
SELECT IF Var1 NE "Total".
EXECUTE.
DELETE VARIABLES TableNumber_ TO Var1 Var3 Total.
RENAME VARIABLES (Var2 @1 @2 @3 = ind_id PS1 PS2 PS3).
RECODE PS1 TO PS3 (1 THRU HIGHEST = 1).
VARIABLE WIDTH ind_id (8).
LIST.

Output from the LIST command:

ind_id PS1 PS2 PS3 
 
1       1   1   1 
2       0   1   1 
3       1   0   0 
4       0   0   1 
5       1   0   1

Number of cases read:  5    Number of cases listed:  5

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

Hello Lyndon.  If CROSSTABS gives you what you want, or nearly so, why not use OMS with CROSSTABS?  Here's an example using the small dataset Robert created in post #2 of the thread. 

* Use Robert's sample dataset.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME raw.

* Use OMS to write crosstabulation to a new dataset called xtab.
DATASET DECLARE  xtab.
OMS
  /SELECT TABLES
  /IF COMMANDS=['Crosstabs'] SUBTYPES=['Crosstabulation']
  /DESTINATION FORMAT=SAV NUMBERED=TableNumber_
   OUTFILE='xtab' VIEWER=YES
  /TAG = "xtab".
CROSSTABS ind_id BY PS.
OMSEND TAG = "xtab".

* Activate dataset xtab & do some clean-up.
DATASET ACTIVATE xtab.
SELECT IF Var1 NE "Total".
EXECUTE.
DELETE VARIABLES TableNumber_ TO Var1 Var3 Total.
RENAME VARIABLES (Var2 @1 @2 @3 = ind_id PS1 PS2 PS3).
RECODE PS1 TO PS3 (1 THRU HIGHEST = 1).
VARIABLE WIDTH ind_id (8).
LIST.

Output from the LIST command:

ind_id PS1 PS2 PS3 
 
1       1   1   1 
2       0   1   1 
3       1   0   0 
4       0   0   1 
5       1   0   1

Number of cases read:  5    Number of cases listed:  5

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)

Hello Lyndon.  If CROSSTABS gives you what you want, or nearly so, why not use OMS with CROSSTABS?  Here's an example using the small dataset Robert created in post #2 of the thread. 

* Use Robert's sample dataset.
NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST/ind_id(F1) PS(F1).
BEGIN DATA
    1 1
    1 2
    1 3
    2 2
    2 3
    3 1
    3 1
    4 3
    5 1
    5 1
    5 3
END DATA.
DATASET NAME raw.

* Use OMS to write crosstabulation to a new dataset called xtab.
DATASET DECLARE  xtab.
OMS
  /SELECT TABLES
  /IF COMMANDS=['Crosstabs'] SUBTYPES=['Crosstabulation']
  /DESTINATION FORMAT=SAV NUMBERED=TableNumber_
   OUTFILE='xtab' VIEWER=YES
  /TAG = "xtab".
CROSSTABS ind_id BY PS.
OMSEND TAG = "xtab".

* Activate dataset xtab & do some clean-up.
DATASET ACTIVATE xtab.
SELECT IF Var1 NE "Total".
EXECUTE.
DELETE VARIABLES TableNumber_ TO Var1 Var3 Total.
RENAME VARIABLES (Var2 @1 @2 @3 = ind_id PS1 PS2 PS3).
RECODE PS1 TO PS3 (1 THRU HIGHEST = 1).
VARIABLE WIDTH ind_id (8).
LIST.

Output from the LIST command:

ind_id PS1 PS2 PS3 
 
1       1   1   1 
2       0   1   1 
3       1   0   0 
4       0   0   1 
5       1   0   1

Number of cases read:  5    Number of cases listed:  5

My data contain two variables, ind_id and PS. There are several records for many ind_id, some have the same and some have different PS. Im trying to make a table with one row per ind_id and one column for each PS with the count of cases in the cells. Crosstabs would do this on a smaller data set. I don't particularly want a display table but want to use the resulting table as data.

What I would normally do is make a crosstable (Crosstabs) and replace values greater than 1 with 1 as I don't actually need the counts: what I need is one row per ind_id and one column for each value of PS (there are three) where the values are zero (no record of that ind_id with that value of PS) or 1 (at least one record of that ind_id with that value of PS).

This should be simple! :-)