Sincerely asking A question related to proc transpose

[septemberbulb]

Hello,

I am trying to transpose the chart one (horizontal)  into the chart two (vertical), by using 

proc transpose data=test;
by project project_sub;
Var a: b: c: ;
run;

however, I wonder whether there are any codes to allow me to transpose into the chart III? thank you

Chart I

Project	Project_Sub	a1	a2	b1	b2	c1	c2
A	success
A	fail
B	success
B	fail
C	success
C	fail

Chart II

Project	Project_Sub	name	value
A	success	a1
A	success	a2
A	success	b1
A	success	b2
A	success	c1
A	success	c2
A	fail	a1
A	fail	a2
A	fail	b1
A	fail	b2
A	fail	c1
A	fail	c2

Chart III

Project	Project_Sub	name_a	value	name_b	value	name_c	value
A	success	a1		b1		c1
A	success	a2		b2		c2
A	fail	a1		b1		c1
A	fail	a2		b2		c2

[Tom · Accepted solution]

So convert it to tall stucture.

Parse the two parts of the _NAME_ into two variables.

Re-order it.

Transpose it again.

data have;
  input Project $ Project_Sub $ a1  a2  b1  b2  c1  c2 ;
cards;
A  success 1 2 3 4 5 6
A  fail    5 6 7 8 9 1
B  success 9 1 2 3 4 5
B  fail    4 5 6 7 8 9
C  success 8 9 1 2 3 4
C  fail    3 4 5 6 7 8
;

proc transpose data=have out=step1;
  by project project_sub notsorted;
run;

data step2;
  set step1;
  suffix = substr(_name_,length(_name_));
  _name_ = substr(_name_,1,length(_name_)-1);
run;
proc sort;
  by project project_sub suffix _name_;
run;

proc transpose data=step2 out=want(drop=_name_);
  by project project_sub suffix ;
  id _name_ ;
  var col1;
run;

[Reeza]

Yes, but it takes multiple steps.

Between Chart2 & 3 split the Name variable so that it has the Letter and the Number separated. Then you can transpose it again. I would recommend a different final structure that may be the same overall though. This would be easier to generate if it would work for you. If the values in NAME may change this isn't quite as your example and is slightly more difficult.

Project	Project_Sub	Order	valueA	valueB	valueC
A	success	1
A	success	2
A	fail	1
A	fail	2

[Tom]

If you know the variable names in advance then do it with a single data step.

data want;
  set have;
  length row 8 a b c 8;
  array old [3,2] a1 -- c2;
  array new [3] a b c ;
  do row=1 to 2;
   do col=1 to 3;
     new[col] = old[col,row];
   end;
   output;
  end;
  drop a1 -- c2 col ;
run;

Result

[ballardw]

You may need to provide some sort of actual example data.

You cannot have two variables with the same name. So you would need to have something besides "value" as the variable name and you really don't save anything that way. What do you expect to do with the III version that can't be done with the II?

A slightly more useful, at least for general purposes table would be something like which could be made with a data step from the data set like Chart I.

Project	Project_Sub	Row	a	b	c
A	success	1
A	success	2
A	fail	1
A	fail	2

[Tom]

If you are reading the data from a source text file, such as a CSV File then just read it that way to start with.

data want;
  input Project $ Project_Sub $ @;
  row=1;
  input (a 2*b 2*c) (:) @;
  output;
  row=2;
  input @1 Project $ Project_Sub $ (2*a 2*b 2*c) (:) ;
  output;
cards;
A  success 1 2 3 4 5 6
A  fail    5 6 7 8 9 1
B  success 9 1 2 3 4 5
B  fail    4 5 6 7 8 9
C  success 8 9 1 2 3 4
C  fail    3 4 5 6 7 8
;

[Ksharp]

data have;
  input Project $ Project_Sub $ a1  a2  b1  b2  c1  c2 ;
cards;
A  success 1 2 3 4 5 6
A  fail    5 6 7 8 9 1
B  success 9 1 2 3 4 5
B  fail    4 5 6 7 8 9
C  success 8 9 1 2 3 4
C  fail    3 4 5 6 7 8
;

proc transpose data=have out=chart1;
  by project project_sub notsorted;
  Var a: b: c: ;
run;
data temp(index=(x=(project project_sub)));
 set chart1;
 name=compress(_name_,,'ka');
run;
proc sql noprint;
select distinct catt('temp(where=(name="',name,'") rename=(_name_=name_',name,' col1=value_',name,') )') 
 into : merge separated by ' '
  from temp ;
quit;
data want;
 merge &merge. ;
 by  project project_sub;
 output;
 call missing(of _all_);
 drop name;
run;