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
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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.