topic Issue transpose in SAS Programming

Issue transpose

Chris_LK_87 — Wed, 07 Jul 2021 09:56:45 GMT

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!

Re: Issue transpose

Kurt_Bremser — Wed, 07 Jul 2021 10:08:14 GMT

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;

Re: Issue transpose

Chris_LK_87 — Wed, 07 Jul 2021 10:31:09 GMT

Thanks!