DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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
Frequent Contributor
Posts: 130

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;

View solution in original post


All Replies
Solution
‎06-25-2015 08:07 PM
Frequent Contributor
Posts: 130

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?

Posted in reply to BruceBrad

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?

Frequent Contributor
Posts: 130

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: 11,343

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

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

26 years...

Super User
Posts: 11,343

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 252 views
  • 0 likes
  • 3 in conversation