topic Re: Reformat datasets: short to long in SAS Programming

Reformat datasets: short to long

michokwu — Wed, 28 Aug 2019 16:14:12 GMT

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,

Re: Reformat datasets: short to long

Reeza — Wed, 28 Aug 2019 16:16:40 GMT

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,

Re: Reformat datasets: short to long

Astounding — Wed, 28 Aug 2019 16:18:57 GMT

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?

Re: Reformat datasets: short to long

michokwu — Wed, 28 Aug 2019 16:28:49 GMT

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

Re: Reformat datasets: short to long

novinosrin — Wed, 28 Aug 2019 16:29:01 GMT

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;

Re: Reformat datasets: short to long

michokwu — Wed, 28 Aug 2019 16:29:15 GMT

Yes, long to wide. Thank you

Re: Reformat datasets: short to long

Reeza — Wed, 28 Aug 2019 16:36:21 GMT

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