## How to make a two-way table to an one-way table?

Solved
Occasional Contributor
Posts: 7

# How to make a two-way table to an one-way table?

Hello, all

Do any of you know how to make a two-way table to an one-way table?

For example,

year        Jan    Feb   March    April   May ...

my dataset:   1993         2        3       2           8        7

1994        6       4      3            2         5

What I want:

y93_1   y93_2    y93_3   y93_4    y93_5   .....y94_1   y94_2    y94_3   y94_4   y94_5....

2        3             2         8           7                6         4           3         2           5

Thank you!

Accepted Solutions
Solution
‎06-25-2015 08:07 PM
Regular Contributor
Posts: 151

## Re: How to make a two-way table to an one-way table?

Apart from some name formatting, this should do the trick. Not sure if it can be done with a single proc transpose.

data temp;

input year jan feb mar;

cards;

1993 2 3 2

1994 6 4 3

;

run;

proc transpose data=temp out=temp2;

by year;

run;

data temp3;

set temp2;

ym = year||_name_; /* change this to format var names */

keep ym col1;

run;

proc transpose data=temp3 out=temp4 (drop=_name_) prefix=y;

var col1;

id ym;

run;

All Replies
Solution
‎06-25-2015 08:07 PM
Regular Contributor
Posts: 151

## Re: How to make a two-way table to an one-way table?

Apart from some name formatting, this should do the trick. Not sure if it can be done with a single proc transpose.

data temp;

input year jan feb mar;

cards;

1993 2 3 2

1994 6 4 3

;

run;

proc transpose data=temp out=temp2;

by year;

run;

data temp3;

set temp2;

ym = year||_name_; /* change this to format var names */

keep ym col1;

run;

proc transpose data=temp3 out=temp4 (drop=_name_) prefix=y;

var col1;

id ym;

run;

Occasional Contributor
Posts: 7

## Re: How to make a two-way table to an one-way table?

Actually last night I tried transpose, and all data became long data. And, I transposed again. (I think it is similar as what you said). It is ok and being one row.

However, my question becomes to: how can I substitute variable names (more than 300 variable names, now all named "COL1-COL300") to the values in the first row?

Regular Contributor
Posts: 151

## Re: How to make a two-way table to an one-way table?

Re variable names: See the ID statement in my code example.

Super User
Posts: 13,583

## Re: How to make a two-way table to an one-way table?

And of course the ever popular what is doing to be done with the output. The starting data set has some issues depending on how it is used and now ..., How many years are you doing this for?

Occasional Contributor
Posts: 7

26 years...

Super User
Posts: 13,583

## Re: How to make a two-way table to an one-way table?

As I asked, what will you do with all of that data on one line in a dataset?

That's why I asked, keeping track of 300+ variables and/or writing code to use them is likely to be mind numbing. And then next year you have another 12 variables to deal with and associated additional logic coding.

For many scenarios having the data in a

ID date value

structure makes more sense, especially if the Date is made as a SAS date valued variable. Then you can use procedures and formats to prepare many types of summaries.

Consider if you wanted to see the total/mean/max/min or similar statistic for months across years with that long data set. How would you do it.

🔒 This topic is solved and locked.