How to transpose and merge two variables at a time

[Ani7]

I have a table that looks like this:

Customer	Year	Widget A	Widget B
Customer A	2001	1	2
Customer A	2002	3	4
Customer B	2001	5	6
Customer B	2002	7	8

And I need to both transpose and merge variables in such a way that it looks like this after:

Customer	Widget A_2001	Widget A_2002	Widget B_2001	Widget B_2002
Customer A	1	3	2	4
Customer B	5	7	6	8

Essentially having only 1 row for each customer. How would I go about doing this? proc transpose doesn't seem to be helping me in this situation.

[ed_sas_member · Accepted solution]

Hi @Ani7 

You can run successively two proc transpose:

data have;
	infile datalines dlm="09"x;
	input Customer :$20.	Year	Widget_A	Widget_B;
	datalines;
Customer A	2001	1	2
Customer A	2002	3	4
Customer B	2001	5	6
Customer B	2002	7	8
;

proc transpose data=have out= have_tr name=widget;
	var Widget_A Widget_B;
	by Customer year;
run;

proc transpose data=have_tr out= want (drop=_name_) delimiter=_;
	var col1;
	id widget year;
	by Customer;
run;

 

Best,

[novinosrin]

@ed_sas_member 's approach is right albeit requires a proc sort in between

proc transpose data=have out=temp ;
by customer year;
var widget:;
run;

proc sort data=temp;
by customer _name_;
run;

proc transpose data=temp out=want(drop=_:) delimiter=_;
by customer;
id _name_ year;
var col1;
run;

[Ksharp]

data have;
	infile datalines expandtabs;
	input Customer :$20.	Year	Widget_A	Widget_B;
	datalines;
CustomerA	2001	1	2
CustomerA	2002	3	4
CustomerB	2001	5	6
CustomerB	2002	7	8
;
proc sql noprint;
select distinct catt('have(where=(year=',year,') rename=(
Widget_A=Widget_A_',year,' Widget_B=Widget_B_',year,' ))') into : merge separated by ' '
from have;
quit;
data want;
 merge &merge;
 by Customer;
 drop year;
run;