Ranking values in Columns in Sequence

[cmoore]

Hi,

I have received an output of data from a survey that was recently sent out. Please see a data example below;

I basically want to reorder the Data by rank order in columns depending what number is in each cell. Any ideas of the best way to do this?

Thanks

Chris

[RW9]

Yep:

1) Normalise the have dataset to 

Respondant_ID Question  Response

...

2) Process the normalised dataset to set the new variable names, i.e. if response=1 then qname="Rank 1"

3) Transpose the data back up again based on the processed new variable name.

Post test data in the form of a datastep for working code.

[ballardw]

---

@cmoore wrote:

Hi,

I have received an output of data from a survey that was recently sent out. Please see a data example below;

I basically want to reorder the Data by rank order in columns depending what number is in each cell. Any ideas of the best way to do this?

Thanks

Chris

---

Have you examined your data so that you can verify that there are never more than 5 "notranked" for any respondent? That none of the respondents ranked any items with the same rank? [ I know that may not have been intended but some surveys / survey software end up allowing such depending on how carefully the data collection staff was trained or the survey script programmed.]

Are there any actually missing values that need to treated differently than "not ranked"?

The values you show imply that the variables would have to be character but there is a chance they are actually numeric and "not ranked" is missing and that text applied with a custom format. Is that the case?

If everything has exactly 5 ranked, with full ranks of 1 to 5, and 5 not ranked, and the variables are in the order as shown and adjacent this might get you started:

Untested as NO data set provided and I'm not going to make one.

data want;
   set have;
   array s Capability -- Reputation;
   array r{10} $15 Rank1-Rank5 NotRanked1-NotRanked5;
   notcount=0;
   do = 1 to dim(s);
      j= input(s[i],f1.);
      if j>0 then r[j]= vname(s[i]);
      else do;
         notcount=notcount+1;
         r[5+notcount] = vname(s[i]);
      end;
   end;
   keep Respondent_id Rank1-Rank5 NotRanked1-NotRanked5;
run;