topic Re: Transpose data in SAS Programming

Transpose data

umesh1 — Tue, 20 Sep 2016 16:33:21 GMT

Hi All,

I have below data set and I would like to transpose using group as in the below resulting data set. Can some one help me, please?

DATA TEST;

INPUT product_cat $ Group $ TOB COUNT;

CARDS;

X A 1 2

X A 2 3

X A 3 4

X A 4 3

X A 5 5

X A 6 5

X A 7 5

X A 8 5

X A 9 5

X A 10 5

X B 1 3

X B 2 5

X B 3 6

X B 4 7

X B 5 3

X B 6 3

X B 7 3

X B 8 3

X B 9 3

X B 10 3

Y A 1 12

Y A 2 13

Y A 3 14

Y A 4 13

Y A 5 15

Y A 6 15

Y A 7 15

Y A 8 15

Y A 9 15

Y A 10 15

Y B 1 13

Y B 2 15

Y B 3 16

Y B 4 17

Y B 5 13

Y B 6 13

Y B 7 13

Y B 8 13

Y B 9 13

Y B 10 13

;

RUN;

resulting data set is as below

product_cat TOB A B

X 1 2 3

X 2 3 5

X 3 4 6

X 4 3 7

X 5 5 3

X 6 5 3

X 7 5 3

X 8 5 3

X 9 5 3

X 10 5 3

Y 1 12 13

Y 2 13 15

Y 3 14 16

Y 4 13 17

Y 5 15 13

Y 6 15 13

Y 7 15 13

Y 8 15 13

Y 9 15 13

Y 10 15 13

Re: Transpose data

Astounding — Tue, 20 Sep 2016 16:44:35 GMT

I'm not sure if this is really a transpose, but it looks like this is what you are after:

data want;

merge test (where=(group='A') rename=(count=A) )

test (where=(group='B') rename=(count=B) );

by product_cat tob;

drop group;

run;

It's untested at this point, so see if it does what you want it to do.

Re: Transpose data

PGStats — Tue, 20 Sep 2016 16:47:28 GMT

Straightforward operation, once dataset is sorted:

data have;
INPUT product_cat $ Group $ TOB COUNT;
CARDS;
X A 1 2
X A 2 3
X A 3 4
X A 4 3
X A 5 5
X A 6 5
X A 7 5
X A 8 5
X A 9 5
X A 10 5
X B 1 3
X B 2 5
X B 3 6
X B 4 7
X B 5 3
X B 6 3
X B 7 3
X B 8 3
X B 9 3
X B 10 3
Y A 1 12
Y A 2 13
Y A 3 14
Y A 4 13
Y A 5 15
Y A 6 15
Y A 7 15
Y A 8 15
Y A 9 15
Y A 10 15
Y B 1 13
Y B 2 15
Y B 3 16
Y B 4 17
Y B 5 13
Y B 6 13
Y B 7 13
Y B 8 13
Y B 9 13
Y B 10 13
;

proc sort data=have; by product_cat TOB; run;

proc transpose data=have out=want(drop=_name_);
by product_cat TOB;
id group;
var count;
run;

proc print data=want noobs; run;

Re: Transpose data

ballardw — Tue, 20 Sep 2016 16:47:59 GMT

Try

proc sort data=test; 
   by product_cat tob;
run;

proc transpose data=test out=testtrans (drop=_name_);
   by product_cat tob;
   id group;
   var count;
run;