How to transpose a sas data set using do loop.

[zqkal]

Does anyone know how I could achieve this.

I have a SAS data with 495 variable and I observation. I want to transpose the variable and create 495 observation. I don’t know how to do this in do loop.

Here is an example

SAS data set

Var1 var1_value  var2 var2_value  var3 var3_value  var4 var4_value

subj    2             owner  0           prop  1              doc    1

I want this

Variable value

Subj          2          

Owner      0

Prop        1

doc          1

Thanks in advance          

[Tom]

Use two arrays.

data want ;

  set have ;

  array name var1 var2 ...... ;

  array number var1_value var2_value .... ;

  do i =1 to dim(name);

       variable = name(i);

       value = number(i);

       output;

  end;

  keep variable value ;

run;

IF all of the character variables have the names and all of the numeric variables have the values AND your variables are defined in a logic order you might be able to avoid listing all of the names.

data want ;

  set have ;

  array name _character_;

  array number _numeric_ ;

...

[art297 · Accepted solution]

Do you really need to do it using a do loop?  It would call for a lot less typing if you created and used a macro variable.  e.g.:

proc sql noprint;

  select "variable="||strip(name)||"; value="||strip(name)||"_value;output;"

    into :varnames separated by " "

      from dictionary.columns

        where libname="WORK" and

              memname="HAVE" and

              name not contains "_value"

  ;

quit;

data want ;

  set have;

  &varnames.;

  keep variable value ;

run;

[mkeintz]

Art:

I don't know that it is really a lot less typing to apply the do loop, as long as there is a way to avoid typing the list of varible names.  One way is the sql way you suggest.

Another is to use Tom's solution, but instead of listing the variables, use the keywords _numeric_ and _character_  as in;

data want (keep=name value);

  set have;
  array nam {*} $32 _character_;
  array val{*}           _numeric_ ;

  do I=1 to dim(nam);

    name=nam{I};

    value=val{I};

    output;

  end;

run;

For folks unfamiliar with the "select ... into"  and dictionary.xxx techniques, the array approach is probably more intuitive.

BUT the downside ... while this technique free of requiring a naming converntion for the variable names  (i.e. you don't need varnames of xxx and xxx_value) , it absolutely depends on the order of the "name" vars to correspond with the order of the numeric value variables.

regards,

Mark

[zqkal]

Thanks everyone

[Haikuo]

Just curious, how come your raw data ends up with an odd number of obs(495)? Does it mean your data does not have a complete pairwise match? The following method is based on the assumption that your data strictly follows Variable-Value order and no skipping, it uses DOW, while Mod() will do the same.

proc transpose data=have out=have1;

var _all_;

run;

data want;

  do _n_=1 to 2;

   set have1;

    if _n_=1 then variable=col1;

  else value=input(col1,best.);

  end;

  keep variable value;

  run;

Haikuo