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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.