Creating a 0-100 score with 3 items - Linear transformation?

[Kim2009]

Greetings - 

I'm trying to figure out how to transform 3 variables so that the range is from 0-100. 

Here are the 3 variables that can also be found in the attached xlsx file. The sum of the 3 variables range between 3 (min) and 12 (max).  

CTIOne

CTITwo

CITThree

Responses for each of the variables were scored as

Strongly Disagree =1; Disagree =2; Agree =3; Strongly Agree =4; 9=Don't know; 99=Missing 

I found an SPSS resource on this topic online but didn't find any SAS documentation that was straightforward. Here are the steps outlined in the SPSS document. 

1. Calculate the sum of responses across the 3 variables (excluding values of 9 and 99) - New variable name = CTI3Sum 

2. Count the number of questions answered, i.e. 1-3 (excluding values of 9 and 99) - New variable name  = CTI3Ct

3. Calculate the mean response - New variable name = CTI3Mean which is calculated by CTI3Sum/CTI3Ct

4. Use linear  transformation to convert to 0-100 score  - New variable name = CTI3_NewScale

Any guidance on how to perform these steps in SAS would be greatly appreciated. 

Best,

Kim

[FreelanceReinh · Accepted solution]

Hello @Kim2009 and welcome to the SAS Support Communities!

The calculation in SAS is relatively easy because the MEAN function excludes missing values by default, hence combines steps 1 - 3. So we only need to replace values 9 and 99 by missing values before calling the MEAN function. This is demonstrated below using artificial test data created in a preliminary DATA step because I don't have Excel on my SAS workstation. (Many experts here won't even download Excel files due to security concerns.)

/* Create test data for demonstration */

data have;
call streaminit(27182818);
length ID 8;
array v[6]  _temporary_ (1:4 9 99);
array p1[6] _temporary_ (.1 .2 .4 3*.1);
array p2[6] _temporary_ (2*.05 3*.25 .15);
array p3[6] _temporary_ (2*.15 2*.3 2*.05);
array score[*] CTIOne CTITwo CTIThree;
do ID=1 to 500;
  score[1]=v[rand('table', of p1[*])];
  score[2]=v[rand('table', of p2[*])];
  score[3]=v[rand('table', of p3[*])];
  output;
end;
run;

/* Create scaled mean score CTI3_NewScale (between 0 and 100) */

data want(drop=_:);
set have;
array score[*] CTIOne CTITwo CTIThree;
array _nmCTI[3];
do _i=1 to dim(score);
  if score[_i] in (1:4) then _nmCTI[_i]=score[_i];
  else if score[_i] in (9, 99) then _nmCTI[_i]=.m;
  else put 'WAR' 'NING: Invalid score for ID ' id  +(-1) ': ' score[_i]=;
end;
if n(of _nmCTI[*]) then CTI3_NewScale=(mean(of _nmCTI[*])-1)*100/3;
else CTI3_NewScale=.;
run;

[Kim2009]

Works like a charm! Thanks a million for your help.