topic Re: Transpose table with fiscal year and frequency in SAS Programming

Transpose table with fiscal year and frequency

mtee15 — Mon, 13 Dec 2021 16:15:09 GMT

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

Re: Transpose table with fiscal year and frequency

Reeza — Mon, 13 Dec 2021 16:19:32 GMT

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

Re: Transpose table with fiscal year and frequency

mtee15 — Mon, 13 Dec 2021 16:21:52 GMT

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.

Re: Transpose table with fiscal year and frequency

ballardw — Mon, 13 Dec 2021 16:22:08 GMT

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.