DATA Step, Macro, Functions and more

Transpose of complex table #2

Accepted Solution Solved
Reply
Regular Contributor
Posts: 210
Accepted Solution

Transpose of complex table #2

[ Edited ]

Hi all,

 

Could you please help to transpose the table below as picture - the algorythm. I have attached a code as well. Should it be proc transpose or arrays are more applicable here. Thank you!

 

 

1.jpg

data test;
	infile datalines;
	input cat1 cat2 min max mean;
datalines;
1	1	1	9	5
1	2	2	7	4
1	3	3	8	5
1	4	2	8	5
1	5	2	9	4
1	6	1	7	5
1	7	2	9	4
1	8	3	8	4
2	1	4	7	5
2	2	1	8	6
2	3	2	9	5
2	4	3	8	6
2	6	3	8	6
2	7	1	7	5
2	8	2	9	5
3	1	2	7	4
3	3	3	8	5
3	4	3	9	6
3	5	1	8	5
3	6	3	9	4
3	7	1	7	5
3	8	2	9	6
;
run; 

 


Accepted Solutions
Solution
‎10-17-2017 08:52 AM
Respected Advisor
Posts: 3,832

Re: Transpose of complex table #2

Posted in reply to DmytroYermak

 

 

proc transpose data=test out=test2 prefix=_;
   by cat1;
   id cat2;
   var min max mean;
   run;

2017-10-17_7-43-59.png

View solution in original post


All Replies
Super User
Super User
Posts: 9,193

Re: Transpose of complex table #2

Posted in reply to DmytroYermak

Like any process, get your data as you want, then process it:

data inter;
  set have;
  select(cat1);
    when (1) res=max; 
    when (2) res=min;
    when (3) res=median;
   otherwise;
  end;
run;

proc transpose data=inter out=want;
  by cat1;
  var res;
  id cat2;
run;

Then you just format cat1 using the text min/max/median 1/2/3.

Regular Contributor
Posts: 210

Re: Transpose of complex table #2

Hi RW9. Thank you for your code. The matter is that the CAT1 does not correspond to Min, Max and Mean. It is just a coincidence. The CAT1 should be left as is.

Solution
‎10-17-2017 08:52 AM
Respected Advisor
Posts: 3,832

Re: Transpose of complex table #2

Posted in reply to DmytroYermak

 

 

proc transpose data=test out=test2 prefix=_;
   by cat1;
   id cat2;
   var min max mean;
   run;

2017-10-17_7-43-59.png

Respected Advisor
Posts: 3,832

Re: Transpose of complex table #2

Posted in reply to data_null__

Use PROC TRANSPOSE NAME= option to change variable _NAME_ to Statistics

 

proc transpose name=statistics ...
Super User
Posts: 10,610

Re: Transpose of complex table #2

Posted in reply to DmytroYermak

It is very simple for PROC TRANSPOSE .

 

data test;
	infile datalines;
	input cat1 cat2 min max mean;
datalines;
1	1	1	9	5
1	2	2	7	4
1	3	3	8	5
1	4	2	8	5
1	5	2	9	4
1	6	1	7	5
1	7	2	9	4
1	8	3	8	4
2	1	4	7	5
2	2	1	8	6
2	3	2	9	5
2	4	3	8	6
2	6	3	8	6
2	7	1	7	5
2	8	2	9	5
3	1	2	7	4
3	3	3	8	5
3	4	3	9	6
3	5	1	8	5
3	6	3	9	4
3	7	1	7	5
3	8	2	9	6
;
run; 
proc transpose data=test out=want;
by cat1;
id cat2;
var min max mean;
run;
Regular Contributor
Posts: 210

Re: Transpose of complex table #2

[ Edited ]

Thank you for the explanations! It has worked.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 107 views
  • 0 likes
  • 4 in conversation