Collapsing Columns

[Jim Moffitt]

I've included a sample dataset that illustrates my question.
 
The sampe data set contains an id variable and seven variables that
contain activity codes for a total of 8 variables.
 
The first two variables following the id variable are named q1.1 and
q1.2.
 
The first (q1.1) contains a value of 1.00 if the respondent has engaged
in that particular activity and is system missing if they didn't engage
in that activity.
 
The second (q1.2) contains a value of 2.00 if the respondent has engaged
in that particular activity and is system missing if they didn't engage
in that activity.
 
This is the usual pattern. All but one of the variables contain either a
system missing value or a value equal to number following the decimal
point in the variable name.
 
The exception is variable q1.2a which contains either a system missing
value or a 96.
 
The remaining variables, like the first two variables following the id
variable all contain either a system missing code or a value equal to
the number following the decimal point in the variable name.
 
Here's what I need to do.
 
I need to create four new variables named Crs1 to Crs4 (note that no
respondent participates in more than 4 activities).
 
I want to populate those variables with either a system misssing code or
the activity codes mentioned by each respondent.
 
Crs1 would be populated with the first activity code mentioned by a
respondent or system missing if the respondent engaged in no activities.
Crs2 would be populated with the second activity code mentioned by a
respondent or system missing if the respondent engaged in one or fewer
activities.
Crs3 would be populated with the third activity code mentioned by a
respondent or system missing if the respondent engaged in two or fewer
activities.
Crs4 would be populated with the fourth activity code mentioned by a
respondent or system missing if the respondent engaged in three or fewer
activities.
 
Thus, referring to the sample data I've provided, Crs1 thru Crs4 would
contain the following values for respondent 101: 1.00, 96.00, 5.00, 7.00
Crs1 thru Crs4 would contain the following values for respondent 102:
2.00, 96.00, 6.00, 7.00
Crs1 thru Crs4 would contain the following values for respondent 103:
4.00, 5.00, system missing, system missing
Crs1 thru Crs4 would contain the following values for respondent 104:
2.00, system missing, system missing, system missing
Crs1 thru Crs4 would contain the following values for respondent 105:
1.00, 2.00, 96.00, 7.00
Crs1 thru Crs4 would contain the following values for respondent 106:
system missing, system missing, 6.00, 7.00
Crs1 thru Crs4 would contain the following values for respondent 106:
system missing, system missing, system missing, 7.00
 
How would I write a macro that would create Crs1 to Crs4 and populate
those variables as above?
 
Here's my sample dataset.
 
DATA LIST LIST (",")/id q1.1 to q1.2 q1.2a q1.4 to q1.7.
BEGIN DATA
101,1,,96,,5,,7
102,,2,96,,,6,7
103,,,,4,5,,,
104,,2,,,,,,
105,1,2,96,,,,7
106,,,,,,6,7
107,,,,,,,7
END DATA.
 
Thanks in advance.
 
 

 

[Art Kendall]

There is example syntax below the sig block.

Save all your current work, then open a new instance of SPSS. Make sure
that you put warnings, etc. into the output file. <edit> <options>
<viewer>. Cut-and-paste then run the syntax.
Does this do what you want?

Hope this helps.

Art
Social Research Consultants


* preserve distinction of user missing and system missing.
set blanks =0./* preserve distinction of user missing and system missing.
DATA LIST LIST (",")/id q1.1 to q1.2 q1.2a q1.4 to q1.7.
BEGIN DATA
101,1,,96,,5,,7
102,,2,96,,,6,7
103,,,,4,5,,,
104,,2,,,,,,
105,1,2,96,,,,7
106,,,,,,6,7
107,,,,,,,7
END DATA.
formats id (f3) q1.1 to q1.7 (f2).
list.
numeric crs1 to crs4 (f2).
do repeat c = crs1 to crs4.
compute c =0.
end repeat.
value labels Q1.1 TO Q1.7 0 'known to be "not CHECKED" '.
do repeat activ= Q1.1 TO Q1.7/testval = 1,2,96,4,5,6,7.
do   if                                           crs1 eq 0 and activ eq
testval.
compute crs1=activ.
else if crs1 ne 0                             and crs2 eq 0 and activ eq
testval.
compute crs2=activ.
else if crs1 ne 0 and crs2 ne 0               and crs3 eq 0 and activ eq
testval.
compute crs3=activ.
else if crs1 ne 0 and crs2 ne 0 and crs3 ne 0 and crs4 eq 0 and activ eq
testval.
compute crs4=activ.
end if.
end repeat.
* execute used so that zero will work in logix without value function.
execute.
missing value crs1 to crs4 (0).
value labels
   crs1 0 'no first  activity mentioned'/
   crs2 0 'no second activity mentioned'/
   crs3 0 'no third  activity mentioned'/
   crs4 0 'no fourth activity mentioned'.
LIST.





Jim Moffitt wrote:

>I've included a sample dataset that illustrates my question.
>
>The sampe data set contains an id variable and seven variables that
>contain activity codes for a total of 8 variables.
>
>The first two variables following the id variable are named q1.1 and
>q1.2.
>
>The first (q1.1) contains a value of 1.00 if the respondent has engaged
>in that particular activity and is system missing if they didn't engage
>in that activity.
>
>The second (q1.2) contains a value of 2.00 if the respondent has engaged
>in that particular activity and is system missing if they didn't engage
>in that activity.
>
>This is the usual pattern. All but one of the variables contain either a
>system missing value or a value equal to number following the decimal
>point in the variable name.
>
>The exception is variable q1.2a which contains either a system missing
>value or a 96.
>
>The remaining variables, like the first two variables following the id
>variable all contain either a system missing code or a value equal to
>the number following the decimal point in the variable name.
>
>Here's what I need to do.
>
>I need to create four new variables named Crs1 to Crs4 (note that no
>respondent participates in more than 4 activities).
>
>I want to populate those variables with either a system misssing code or
>the activity codes mentioned by each respondent.
>
>Crs1 would be populated with the first activity code mentioned by a
>respondent or system missing if the respondent engaged in no activities.
>Crs2 would be populated with the second activity code mentioned by a
>respondent or system missing if the respondent engaged in one or fewer
>activities.
>Crs3 would be populated with the third activity code mentioned by a
>respondent or system missing if the respondent engaged in two or fewer
>activities.
>Crs4 would be populated with the fourth activity code mentioned by a
>respondent or system missing if the respondent engaged in three or fewer
>activities.
>
>Thus, referring to the sample data I've provided, Crs1 thru Crs4 would
>contain the following values for respondent 101: 1.00, 96.00, 5.00, 7.00
>Crs1 thru Crs4 would contain the following values for respondent 102:
>2.00, 96.00, 6.00, 7.00
>Crs1 thru Crs4 would contain the following values for respondent 103:
>4.00, 5.00, system missing, system missing
>Crs1 thru Crs4 would contain the following values for respondent 104:
>2.00, system missing, system missing, system missing
>Crs1 thru Crs4 would contain the following values for respondent 105:
>1.00, 2.00, 96.00, 7.00
>Crs1 thru Crs4 would contain the following values for respondent 106:
>system missing, system missing, 6.00, 7.00
>Crs1 thru Crs4 would contain the following values for respondent 106:
>system missing, system missing, system missing, 7.00
>
>How would I write a macro that would create Crs1 to Crs4 and populate
>those variables as above?
>
>Here's my sample dataset.
>
>DATA LIST LIST (",")/id q1.1 to q1.2 q1.2a q1.4 to q1.7.
>BEGIN DATA
>101,1,,96,,5,,7
>102,,2,96,,,6,7
>103,,,,4,5,,,
>104,,2,,,,,,
>105,1,2,96,,,,7
>106,,,,,,6,7
>107,,,,,,,7
>END DATA.
>
>Thanks in advance.
>
>
>
>
>
>
>
>