PROC TRANPOSE with "multiple VAR" statements

[filippo_kow]

 Hi guys,

I have a little problem. I have the following dataset:

and in the results I would like to obtain duplicated record for x1/x2 and y1/y2 variables, it means something like this:

If I will have only one variable, then I would simply use:

proc transpose data=test out=tr;
by trans1 trans2;
var x1 x2;
run;

But the problem is with multiple variables.

Can you please help me how can I obtain the result (using proc transpose or something else) ?

Thanks in advance!

[Tom · Accepted solution]

Do them separately and combine.

proc transpose data=test out=X;
by trans1 trans2;
var x1 x2;
run;
proc transpose data=test out=X(rename=(_name_=_name2_ col1=col2));
by trans1 trans2;
var x1 x2;
run;
data want;
  merge x y;
  by trans1 trans2;
run;

Or just code it yourself.

data want;
  set test ;
  array _x x1-x2;
  array _y y1 y2;
  do index=1 to dim(_x) ;
     x=_x[index];
     y=_y[index];
     output;
   end;
  drop x1-x2 y1-y2;
run;

[filippo_kow]

 Hi,

Thanks for the info!

That's deffinetely good solution, but the problem occurs when you have many "col_n" and variables - then I need to make many single transposes and merge by many columns. I just thought that maybe there is "one-step" solution of this problem 🙂

[filippo_kow]

 Hi again Tom,

I missed your second data step - it is exactly what I meant - thanks a lot!!!

 Cheers,

 Filip

[Tom]

---

@filippo_kow wrote:

 Hi,

Thanks for the info!

That's deffinetely good solution, but the problem occurs when you have many "col_n" and variables - then I need to make many single transposes and merge by many columns. I just thought that maybe there is "one-step" solution of this problem 🙂

---

Perhaps you can make a better example?

You can also transform to TALL format.  Parse out the NAME and the NUMBER from the name and reorder and transform back.

proc transpose data=test out=tall ;
  by id1 id2 ;
  var x1 x2 y1 y2 z1 z2 ;
run;

data step2;
  set tall;
  name=substr(_name_,1,length(_name_)-1);
  number = char(_name_,length(_name_));
run;

proc sort data=step2;
  by id1 id2 number name ;
run;

proc transpose data=step2 out=want;
  by id1 id2 number ;
  id name;
  var col1;
run;

Parsing your real variable names might be harder.

[Ksharp]

data have;
input  (t1 t2 x1 x2 x3 y1 y2 y3 z1 z2 z3) ($);
cards;
xyz xyz 111 222 333 888 999 777 1 2 3
;

proc transpose data=have out=temp;
by t1 t2;
var x1--z3;
run;
data temp;
 set temp;
 id=scan(_name_,1,,'d');
run;
proc sql noprint;
select distinct catt('temp(where=(id="',id,'") rename=(_name_=name_',id,' col1=col_',id,'))') into :merge separated by ' ' from temp;
quit;

data want;
 merge &merge.;
 by t1 t2;
 output;
 call missing(of _all_);
drop id;
run;

[PaigeMiller]

You can use the %UNTRANSPOSE macro

ABSTRACT
PROC TRANSPOSE is an extremely powerful tool for making long files wide, and wide files less wide or
long, but getting it to do what you need often involves a lot of time, effort, and a substantial knowledge of
SAS® functions and data step processing. This is especially true when you have to untranspose a wide
file that contains both character and numeric variables. And, while the procedure usually seamlessly
handles variable types, lengths and formats, it doesn’t always do that and only creates a system variable
(i.e., _label_) to capture variable labels. The present paper introduces a macro that simplifies the process,
significantly reduces the amount of coding and programming skills needed (thus reducing the likelihood of
producing the wrong result), runs up to 50 or more times faster than the multiple PROC TRANSPOSE
and data steps that would otherwise be needed, and either creates untransposed variables that inherit all
of the original variables’ characteristics or creates a file that contains all of the relevant metadata.

https://support.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2419-2018.pdf