topic Re: Transpose of complex table #2 in SAS Programming

Transpose of complex table #2

DmytroYermak — Tue, 17 Oct 2017 12:40:45 GMT

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!

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;

Re: Transpose of complex table #2

RW9 — Tue, 17 Oct 2017 12:40:34 GMT

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.

Re: Transpose of complex table #2

DmytroYermak — Tue, 17 Oct 2017 12:45:36 GMT

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.

Re: Transpose of complex table #2

data_null__ — Tue, 17 Oct 2017 12:46:37 GMT

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

Re: Transpose of complex table #2

Ksharp — Tue, 17 Oct 2017 12:51:42 GMT

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;

Re: Transpose of complex table #2

DmytroYermak — Tue, 17 Oct 2017 12:53:31 GMT

Thank you for the explanations! It has worked.

Re: Transpose of complex table #2

data_null__ — Tue, 17 Oct 2017 12:59:38 GMT

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

proc transpose name=statistics ...