topic Reverse Transpose in SAS Procedures

Reverse Transpose

DavidPhillips2 — Wed, 30 Aug 2017 20:10:48 GMT

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.

Re: Reverse Transpose

ballardw — Wed, 30 Aug 2017 20:15:58 GMT

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.

Re: Reverse Transpose

novinosrin — Wed, 30 Aug 2017 20:16:42 GMT

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

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;

Re: Reverse Transpose

Reeza — Wed, 30 Aug 2017 20:18:13 GMT

PROC TRANSPOSE goes in both directions.

Re: Reverse Transpose

art297 — Wed, 30 Aug 2017 20:19:04 GMT

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

Re: Reverse Transpose

DavidPhillips2 — Wed, 30 Aug 2017 20:19:46 GMT

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

Re: Reverse Transpose

DavidPhillips2 — Wed, 30 Aug 2017 20:26:40 GMT

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.

Re: Reverse Transpose

Reeza — Wed, 30 Aug 2017 20:30:03 GMT

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 🙂

Re: Reverse Transpose

Rick_SAS — Thu, 31 Aug 2017 20:05:55 GMT

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.

Re: Reverse Transpose

art297 — Sat, 06 Jan 2018 00:59:48 GMT

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