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!!!
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
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;
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
even simpler! Thank you!!!
WOW!!! So simple and works great! Thank you!!!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.