Transpose table with fiscal year and frequency

[mtee15]

Hello,

I have this table:

Fruit	Country	Colour	Frequency	FY
Apple	Canada	Red	34	201516
Apple	Canada	Red	39	201617
Apple	Canada	Red	43	201819
Apple	Canada	Red	36	201920

And it want it to display like this:

Fruit	Country	Colour	201516	201617	201718	201819	201920
Apple	Canada	Red	34	29	.	43	36

[Reeza · Accepted solution]

proc transpose data=have out=wide prefix = FY;
by fruit country colour;
id fy;
var frequency;
run;

However, this will not add in missing values for you, that needs to happen in another way.  Do you have a table or format that has all possible values for the dates? Note that 201516 is not a valid SAS name, so I added prefix=FY so it would get named FY201516 instead.

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/

---

@mtee15 wrote:

Hello,

I have this table:

Fruit	Country	Colour	Frequency	FY
Apple	Canada	Red	34	201516
Apple	Canada	Red	39	201617
Apple	Canada	Red	43	201819
Apple	Canada	Red	36	201920

And it want it to display like this:

Fruit	Country	Colour	201516	201617	201718	201819	201920
Apple	Canada	Red	34	29	.	43	36

---

[mtee15]

Thanks I will add the FY prefix to the years. How can I account for this missing values?

fruit, country and colour = character 

frequency= numeric

FY=numeric to be changed to character now.

[ballardw]

Since you don't provide any rules we need to ask some questions like what might constitute a report group between the Fruit, Country and Color? You only show one value of each so it is not clear what options may be needed when other values appear.

Basic approach could be Proc Report with the FY variable as an across variable and frequency reporting with it.