DATA Step, Macro, Functions and more

How to transpose a sas data set using do loop.

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

How to transpose a sas data set using do loop.

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          


Accepted Solutions
Solution
‎08-26-2013 03:49 PM
PROC Star
Posts: 7,471

Re: How to transpose a sas data set using do loop.

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,039

Re: How to transpose a sas data set using do loop.

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_ ;

...

Solution
‎08-26-2013 03:49 PM
PROC Star
Posts: 7,471

Re: How to transpose a sas data set using do loop.

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;

Trusted Advisor
Posts: 1,019

Re: How to transpose a sas data set using do loop.

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

Contributor
Posts: 47

Re: How to transpose a sas data set using do loop.

Thanks everyone

Respected Advisor
Posts: 3,156

Re: How to transpose a sas data set using do loop.

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 558 views
  • 0 likes
  • 5 in conversation