topic Re: How to make a two-way table to an one-way table? in SAS Programming

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

Seashore — Thu, 25 Jun 2015 18:42:39 GMT

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!

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

BruceBrad — Fri, 26 Jun 2015 00:07:01 GMT

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;

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

ballardw — Fri, 26 Jun 2015 15:36:39 GMT

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?

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

Seashore — Fri, 26 Jun 2015 17:25:00 GMT

26 years...

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

Seashore — Fri, 26 Jun 2015 17:29:01 GMT

Thank you, BruceBrad!

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?

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

ballardw — Fri, 26 Jun 2015 19:18:14 GMT

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.

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

BruceBrad — Fri, 26 Jun 2015 23:57:13 GMT

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