I have a dataset that contains data by city and month. The numerical columns contain sales figure for each year and an average. I have tried to use proc transpose but I am not sure that I am doing it right. Or is there an other solution?
data have;
;
input city$ month$ year2017 year2018 year2019 mean2017_2019;
datalines;
London Jan 10 15 20 15
Rom Jan 20 25 30 25
Paris Jan 20 10 10 13.33
Berlin Jan 50 60 70 60
London Feb 20 30 40 30
Rom Feb 40 50 60 50
Paris Feb 40 20 20 26.67
Berlin Feb 100 90 50 80
;
run;
I would like to create a table that looks like this:
Data want;
city$ year$ month$ sales mean2017_2019
Berlin 2017 Jan 50 60
Berlin 2018 Jan 60 60
Berlin 2019 Jan 70 60
Berlin 2017 Feb 100 80
Berlin 2018 Feb 90 80
Berlin 2019 Feb 50 80
London 2017 Jan 10 15
etc.
Thanks!
See this:
proc sort data=have;
by city;
run;
proc transpose data=have out=long (rename=(col1=sales));
by city month notsorted mean2017_2019;
var year:;
run;
data want;
retain city year month sales mean2017_2019; /* only for variable order */
set long;
length year $4;
year = substr(_name_,5);
drop _name_;
run;
See this:
proc sort data=have;
by city;
run;
proc transpose data=have out=long (rename=(col1=sales));
by city month notsorted mean2017_2019;
var year:;
run;
data want;
retain city year month sales mean2017_2019; /* only for variable order */
set long;
length year $4;
year = substr(_name_,5);
drop _name_;
run;
Thanks!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: