Proc Transpose Issue

[art297]

: Here is yet one more try, this time independent of the number of variables and with a final reordering of the resulting variable names.  I did, however, change the spelling of "response".  The only thing that would have to be changed is the initial maximum number of response variables if it is ever greater than 999:

data have;

  infile cards truncover;

  input (Id Question response1-response999) ($);

  cards;

fgg45 A1 YES

fgg45 S1 YES

fgg45 S2 NO NO NO NO NO NO

fgg45 G5 YES YES YES YES YES YES YES

fgg45 G6 NO

gg453 S1 YES YES

gg453 S2 NO NO NO NO NO NO NO

gg453 G5 YES YES YES

gg453 G6 NO NO NO NO

;

proc sort data=have;

by id question;

run;

proc transpose data=have

  out=need (where=(not missing(col1)));

  by id question;

  var response:;

run;

data need (drop=question);

  set need;

  _name_=catt(Question,"_",substr(_name_,anydigit(_name_)));

run;

proc sql noprint;

  select _name_ into :reorder

    separated by " "

      from need

        order by _name_

  ;

quit;

proc transpose data=need out=want (drop=_:);

  by id;

  var col1;

run;

data want;

  retain id &reorder.;

  set want;

run;