BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mtee15
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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


 

View solution in original post

3 REPLIES 3
Reeza
Super User
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
Calcite | Level 5

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
Super User

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.

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 609 views
  • 2 likes
  • 3 in conversation