Trouble with PROC TRANSPOSE

[bendsteel6]

For the life of me, I can't figure this out!

Using PROC TRANSPOSE, I'm trying to get from this (hopefully these tables show up correctly):

MAIN_CITY_NM	GEO_OPEN_TO_RO_OPEN_HRS_Median	RO_OPEN_TO_RO_START_HRS_Median	RO_START_TO_RO_FINISH_H_Median	RO_FINISH_TO_GEO_END_HR_Median	GEO_OPEN_DAYS_Median
CONSHOHOCKEN	1.3	2.3	50.8	30.5	5.5
PENNSAUKEN	1.8	6.3	27.3	2.5	1.3
VINELAND	1.7	0.1	128.4	1.5	3.1
TRENTON	2.5	0.3	47.3	14.7	3.1
NEW CASTLE	2	5.5	29.4	3	5
SOUDERTON	-2	2.9	2.8	0.3	0.2

to this:

MAIN_CITY_NM	Analysis_Variable	Value
CONSHOHOCKEN	GEO_OPEN_TO_RO_OPEN_HRS_Median	1.3
CONSHOHOCKEN	RO_OPEN_TO_RO_START_HRS_Median	2.3
CONSHOHOCKEN	RO_START_TO_RO_FINISH_H_Median	50.8
CONSHOHOCKEN	RO_FINISH_TO_GEO_END_HR_Median	30.5
CONSHOHOCKEN	GEO_OPEN_DAYS_Median	5.5
PENNSAUKEN	GEO_OPEN_TO_RO_OPEN_HRS_Median	1.8
PENNSAUKEN	RO_OPEN_TO_RO_START_HRS_Median	6.3
PENNSAUKEN	RO_START_TO_RO_FINISH_H_Median	27.3
PENNSAUKEN	RO_FINISH_TO_GEO_END_HR_Median	2.5
PENNSAUKEN	GEO_OPEN_DAYS_Median	1.3
VINELAND	GEO_OPEN_TO_RO_OPEN_HRS_Median	1.7
VINELAND	RO_OPEN_TO_RO_START_HRS_Median	0.1
VINELAND	RO_START_TO_RO_FINISH_H_Median	128.4
VINELAND	RO_FINISH_TO_GEO_END_HR_Median	1.5
VINELAND	GEO_OPEN_DAYS_Median	3.1
TRENTON	GEO_OPEN_TO_RO_OPEN_HRS_Median	2.5
TRENTON	RO_OPEN_TO_RO_START_HRS_Median	0.3
TRENTON	RO_START_TO_RO_FINISH_H_Median	47.3
TRENTON	RO_FINISH_TO_GEO_END_HR_Median	14.7
TRENTON	GEO_OPEN_DAYS_Median	3.1
NEW CASTLE	GEO_OPEN_TO_RO_OPEN_HRS_Median	2
NEW CASTLE	RO_OPEN_TO_RO_START_HRS_Median	5.5
NEW CASTLE	RO_START_TO_RO_FINISH_H_Median	29.4
NEW CASTLE	RO_FINISH_TO_GEO_END_HR_Median	3
NEW CASTLE	GEO_OPEN_DAYS_Median	5
SOUDERTON	GEO_OPEN_TO_RO_OPEN_HRS_Median	-2
SOUDERTON	RO_OPEN_TO_RO_START_HRS_Median	2.9
SOUDERTON	RO_START_TO_RO_FINISH_H_Median	2.8
SOUDERTON	RO_FINISH_TO_GEO_END_HR_Median	0.3
SOUDERTON	GEO_OPEN_DAYS_Median	0.2

For some reason, I just can't figure it out.  Is there anyone out there who's REALLY GOOD with PROC TRANSPOSE who could have a stab at this?

THANKS!!!

[Puwang]

The following code works (a is the name of your original table):

proc sort data = work.a;
	by MAIN_CITY_NM;
run;
proc transpose data = work.a out=work.a_trans;
	by MAIN_CITY_NM;
run;

[art297 · Accepted solution]

Basically the same answer as @Puwang, but you don't need to sort your data and the following accomplishes the renaming of variables according to your example:

proc transpose data = have out=want (rename=(col1=value)) name=analysis_variable;
	by MAIN_CITY_NM notsorted;
run;

Art, CEO, AnalystFinder.com

[bendsteel6]

even simpler!  Thank you!!!

[bendsteel6]

WOW!!! So simple and works great!  Thank you!!!