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.
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,
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,
@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;
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;
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.