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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
BruceBrad
Lapis Lazuli | Level 10

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

6 REPLIES 6
BruceBrad
Lapis Lazuli | Level 10

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;

Seashore
Calcite | Level 5

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?

BruceBrad
Lapis Lazuli | Level 10

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

ballardw
Super User

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?

ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 6 replies
  • 1026 views
  • 0 likes
  • 3 in conversation