Reformat datasets: short to long

[michokwu]

Hi,

I am trying to reformat a really large dataset for better analysis

what I have
Year	Manager	Sales (#)
2015	Alan	360
2015	James	190
2015	Elena	220
2015	McCarthy	470
2016	Alan	387
2016	James	202
2016	Elena	250
2016	McCarthy	515
2017	Alan
2017	James
2017	Elena
2017	McCarthy
what I want
Manager	2015	2016	2017
Alan	360	387
James	190	202
Elena	220	250
McCarthy	470	515

Thank you,

[Reeza · Accepted solution]

That's from long to wide. A standard proc transpose is your easiest solution.

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

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

---

@michokwu wrote:

Hi,

I am trying to reformat a really large dataset for better analysis

what I have
Year	Manager	Sales (#)
2015	Alan	360
2015	James	190
2015	Elena	220
2015	McCarthy	470
2016	Alan	387
2016	James	202
2016	Elena	250
2016	McCarthy	515
2017	Alan
2017	James
2017	Elena
2017	McCarthy
what I want
Manager	2015	2016	2017
Alan	360	387
James	190	202
Elena	220	250
McCarthy	470	515

Thank you,

---

[michokwu]

Yes, long to wide. Thank you

[Astounding]

Your current form of the data will support most analyses.  What type of analysis would you want to perform, where you need to transpose the data?

[michokwu]

Need to observe trends over time. The transpose works. Thank you

[Reeza]

To graph that data you would want it in the current form. It's also easier to calculate month over month/year over year/year to date calculations in the long format. 

The wide format is really only useful for appearances in my experience. 

---

@michokwu wrote:

Need to observe trends over time. The transpose works. Thank you

---

[novinosrin]

data have;
infile cards expandtabs truncover;
input Year 	Manager $	Sales 	 ;
cards;
2015	Alan	360	 
2015	James	190	 
2015	Elena	220	 
2015	McCarthy	470	 
2016	Alan	387	 
2016	James	202	 
2016	Elena	250	 
2016	McCarthy	515	 
2017	Alan	 	 
2017	James	 	 
2017	Elena	 	 
2017	McCarthy	 
;
proc sort data=have out=_have;
by manager year;
run;
proc transpose data=_have out=want(drop=_name_) prefix=Year_;
by manager;
var sales;
id year;
run;