Help using Base SAS procedures

Reverse Transpose

Reply
Valued Guide
Posts: 617

Reverse Transpose

I’m looking at proc transpose.  After I use it I’m looking at making a minor update.  Is there a proc to reverse transpose?  I’d like to keep all the same column names and column lengths.

Super User
Posts: 13,542

Re: Reverse Transpose

Posted in reply to DavidPhillips2

A concrete example of the start data and the transpose used would help.

Often a set created by transpose can be transposed back if keep the _name_ and _label_ from the first transpose and then use those as the ID and IDLABEL variables on the second transpose.

PROC Star
Posts: 1,803

Re: Reverse Transpose

Posted in reply to DavidPhillips2

Nice question. Will this illustration help? I'm learning too. I'll await better examples with you Smiley Happy

 

 

data have;

input col1 & $ col2 dollar5.;

datalines;

Cost 1     $100

Cost 2     $200

Cost 3     $300

;

/*long to wide*/

proc transpose data=have out=wide ;

    id col1;

    var col2;

run;

/*wide to long*/

proc transpose data=wide out=long ;

    id _name_;  

run;

Super User
Posts: 23,722

Re: Reverse Transpose

Posted in reply to DavidPhillips2

PROC TRANSPOSE goes in both directions. 

 

Valued Guide
Posts: 617

Re: Reverse Transpose

Thanks for the good news.  Is there a transpose all columns option?

PROC Star
Posts: 8,164

Re: Reverse Transpose

Posted in reply to DavidPhillips2

Yes, there is a reverse proc transpose, but it will likely destroy you column widths and possibly even variable types. An alternative is to create separate files for all of your transposed variables, and then merge them back together after making your corrections.

 

Art, CEO, AnalystFinder.com

 

Valued Guide
Posts: 617

Re: Reverse Transpose

Given the advice in this thread, it sounds like it would be a lot of work to transpose a table with 100 variables and transpose it back.  It looks like the column names and lengths would be weird after putting it back.

Super User
Posts: 23,722

Re: Reverse Transpose

Posted in reply to DavidPhillips2

You could force it. I think the names would likely translate back directly but the lengths may be iffy.

 

Assuming you were starting off from a specific format you could create a table like that, using LIKE via SQL and then insert the transposed data. Though if you had that, why do this in the first place Smiley Happy

SAS Super FREQ
Posts: 4,241

Re: Reverse Transpose

Posted in reply to DavidPhillips2

The techniques you are looking for can be searched for with the terms

transpose "long to wide" data sas

and

transpose "wide to long" data sas

 

There are many, many solutions, including the %MAKEWIDE and %MAKELONG macros.

 

PROC Star
Posts: 8,164

Re: Reverse Transpose

Posted in reply to DavidPhillips2

My response is only about 5 months late, but take a look at: https://communities.sas.com/t5/SAS-Communities-Library/How-to-untranspose-a-wide-file/ta-p/425467

 

Your post motivated us to write and publish a macro to solve the problem

 

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 9 replies
  • 1580 views
  • 4 likes
  • 6 in conversation