Proc Transpose from long to wide

[SASLearner7]

I would like to transpose a long dataset to a fully multi-variate one while adding suffix of "_X" (where X is the trial number) according to the values contained in the trial variable the new variables it will create (ie. create variable trial_1, trial_2, trial_3, etc).  The trial variable is always going to be in a sequence and will not skip and will always have the same number of trials per id.  The original dataset will have a few hundred variables, so the resulting transposed dataset is going to be really "wide".

ID	trial	time	score	location
111	1	123	10	a
111	2	456	10	b
111	3	789	11	c
222	1	234	10	a
222	2	567	10	c
222	3	890	12	c
333	1	123	9	x
333	2	234	8	y
333	3	345	7	z

and would like the output to like:

ID	trial_1	trial_2	trial_3	time_1	time_2	time_3	score_1	score_2	score_3	location_1	location_2	location_3
111	1	2	3	123	456	789	10	10	11	a	b	c
222	1	2	3	234	567	890	10	10	12	a	c	c
333	1	2	3	123	234	345	9	8	7	x	y	z

Thank you.

[Reeza · Accepted solution]

This is example 5 here - do three transposes and merge.

https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

If you want a single step solution, there's a macro here that does what you need:

https://communities.sas.com/t5/SAS-Communities-Library/A-better-way-to-FLIP-i-e-transpose-make-wide-...

Or a third approach, a double transpose - first to a more long format and then to the wide format - not 100% sure this would work directly:

https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

If you're still having trouble please post the code and the log.

---

@SASLearner7 wrote:

I would like to transpose a long dataset to a fully multi-variate one while adding suffix of "_X" (where X is the trial number) according to the values contained in the trial variable the new variables it will create (ie. create variable trial_1, trial_2, trial_3, etc).  The trial variable is always going to be in a sequence and will not skip and will always have the same number of trials per id.  The original dataset will have a few hundred variables, so the resulting transposed dataset is going to be really "wide".

ID	trial	time	score	location
111	1	123	10	a
111	2	456	10	b
111	3	789	11	c
222	1	234	10	a
222	2	567	10	c
222	3	890	12	c
333	1	123	9	x
333	2	234	8	y
333	3	345	7	z

and would like the output to like:

ID	trial_1	trial_2	trial_3	time_1	time_2	time_3	score_1	score_2	score_3	location_1	location_2	location_3
111	1	2	3	123	456	789	10	10	11	a	b	c
222	1	2	3	234	567	890	10	10	12	a	c	c
333	1	2	3	123	234	345	9	8	7	x	y	z

Thank you.

---

[ballardw]

---

@SASLearner7 wrote:

I would like to transpose a long dataset to a fully multi-variate one while adding suffix of "_X" (where X is the trial number) according to the values contained in the trial variable the new variables it will create (ie. create variable trial_1, trial_2, trial_3, etc).  The trial variable is always going to be in a sequence and will not skip and will always have the same number of trials per id.  The original dataset will have a few hundred variables, so the resulting transposed dataset is going to be really "wide".

---

I would question the need for this if you already have "a few hundred variables" as you are likely to end up with "several thousands of variables".

Which can make writing any sort of code to work more than a little challenging.

Can you describe what you will be doing with the transposed version of this data? Often there are other approaches.

For example, we have seen requests to make a wide table like this for a report when one of the report procedures like Proc Report or Tabulate will create a "wide" table and provide nice formatting at the same time.

[SASLearner7]

Thank you for all your help.  This was a data request for a customer and is what they have asked for to be able to do their analysis.

[ballardw]

---

@SASLearner7 wrote:

Thank you for all your help.  This was a data request for a customer and is what they have asked for to be able to do their analysis.

---

'Nuff said, this follows the Golden Rule: He who has the gold makes the rules.

But I have had some pretty silly customers in the past...

[Ksharp]

1) proc summary

data have;
infile cards expandtabs;
input ID	trial	time	score	location $;
cards;
111	1	123	10	a
111	2	456	10	b
111	3	789	11	c
222	1	234	10	a
222	2	567	10	c
222	3	890	12	c
333	1	123	9	x
333	2	234	8	y
333	3	345	7	z
;
run;

proc sql noprint;
select max(n) into : n
 from (select count(*) as n from have group by id);
quit;
proc summary data=have ;
by id;
output out=want idgroup(out[&n] (trial	time	score	location)=);
run;

[Ksharp]

"Transpose Datset by MERGE"
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf