topic Re: Changing column order after combining multiple transposes in SAS Programming

Changing column order after combining multiple transposes

tmcwill — Thu, 01 Oct 2020 19:59:31 GMT

I am attempting to transform a dataset that has the following layout:

data have ; 
input ID type$ baseline peak diff ; 
cards ; 
1 A 5 6 1 
1 B 7 4 -3 
2 A 12 3 -9
2 B 3 3 0 
3 A 4 8 4 
3 B 1 8 7 
; 
run ;

Using the following code:

proc transpose data=have out=baseline prefix=baseline ; 
by ID ; 
ID type ; 
var baseline ; 
run;

proc transpose data=have out=peak prefix=peak ; 
by ID ; 
ID type ; 
var peak; 
run ;

proc transpose data=have out=diff prefix=diff ; 
by ID ; 
ID type ; 
var diff ; 
run ;

data combined ; 
merge baseline peak diff ; 
by ID ; 
run ;

The resulting dataset has this format:

data combined ; 
input ID baselineA baselineB peakA peakB diffA diffB; 
cards ; 
1 5 7 6 4 1 -3 
2 12 3 3 3 -9 0 
3 4 1 8 8 4 7 
; 
run ;

This is mostly the format I want, however I need the variables in a slightly different order, as follows:

data want ; 
	input ID baselineA peakA diffA baselineB peakB diffB ; 
	cards ; 
	1 5 6 1 7 4 -3 
	2 12 3 -9 3 3 0 
	3 4 8 4 1 8 7 
	; 
run ;

Basically, I variables for the same type (A,B) from each transpose listed sequentially.

One thing to note is that with the above example I could easily just reorder the variables using the retain statement. However, in my actual dataset rather than just having two types (A and B), there are 31 categories, and there are 4 rather than 3 transposes. This means 124 (31x4) columns to reorder, which would be a bit tedious.

What is the most efficient way to reorder the columns like I want? Is there a better method than transposing separately 3 times?

Thanks in advance!

Re: Changing column order after combining multiple transposes

CurtisMackWSIPP — Thu, 01 Oct 2020 20:03:06 GMT

Declare them in the order you need before the input, set, or merge statement. A length or format statement will work.

length ID baselineA peakA diffA baselineB peakB diffB 8;

Re: Changing column order after combining multiple transposes

CurtisMackWSIPP — Thu, 01 Oct 2020 20:04:45 GMT

An ATTRIB statement also works.

Re: Changing column order after combining multiple transposes

tmcwill — Thu, 01 Oct 2020 20:30:03 GMT

Thanks for the response! My issue with this is that there are 124 variables, and I don't want to have to type 124 variables in the length/retain/attribute statement.

Re: Changing column order after combining multiple transposes

ballardw — Thu, 01 Oct 2020 20:46:36 GMT

@tmcwill wrote:

Thanks for the response! My issue with this is that there are 124 variables, and I don't want to have to type 124 variables in the length/retain/attribute statement.

Consider this example;

data have ; 
input ID type$ baseline peak diff ; 
cards ; 
1 A 5 6 1 
1 B 7 4 -3 
2 A 12 3 -9
2 B 3 3 0 
3 A 4 8 4 
3 B 1 8 7 
; 
run ;
proc transpose data=have out=trans;
   by id type;
run;

proc transpose data=trans out=trans2;
   by id;
   id _name_ type;
   var col1;
run;

And will save doing a bunch of transposes as well.

Re: Changing column order after combining multiple transposes

CurtisMackWSIPP — Thu, 01 Oct 2020 21:24:53 GMT

Then my response is why do you care what order they are in? You can fix the order in whatever output process you use.

How would the system know what order you want until you tell it?

@ballardw wrote:

@tmcwill wrote:

Thanks for the response! My issue with this is that there are 124 variables, and I don't want to have to type 124 variables in the length/retain/attribute statement.

Re: Changing column order after combining multiple transposes

CurtisMackWSIPP — Thu, 01 Oct 2020 21:26:09 GMT

Or, write a little MACRO to save some typing.

Re: Changing column order after combining multiple transposes

ballardw — Fri, 02 Oct 2020 15:12:19 GMT

@CurtisMackWSIPP wrote:

Then my response is why do you care what order they are in? You can fix the order in whatever output process you use.

How would the system know what order you want until you tell it?

@ballardw wrote:

@tmcwill wrote:

Thanks for the response! My issue with this is that there are 124 variables, and I don't want to have to type 124 variables in the length/retain/attribute statement.

I've given up on asking the "what difference does the order make". To many Excel contaminated brains.😉

My response was basically to get rid of the multiple proc transpose calls in the spirit of efficiency and just happens to address the order issue.

Re: Changing column order after combining multiple transposes

tmcwill — Fri, 02 Oct 2020 15:23:19 GMT

Thanks, this is great! I figured there had to be a more efficient way to do this than doing each transpose individually, but I couldn't quite find what I was looking for online. And as you mentioned, it also fixes the variable order issue.

Thanks again!

Re: Changing column order after combining multiple transposes

tmcwill — Fri, 02 Oct 2020 15:25:32 GMT

Hi, thanks again for your responses. Just an FYI, normally I don't care what order variables are in for my own analyses, but I am doing this for another researcher who needs the data in a very specific format in order to use the dataset as an input in an exterior program that will generate a complex figure/plot.

Re: Changing column order after combining multiple transposes

ballardw — Fri, 02 Oct 2020 23:25:22 GMT

@tmcwill wrote:

Hi, thanks again for your responses. Just an FYI, normally I don't care what order variables are in for my own analyses, but I am doing this for another researcher who needs the data in a very specific format in order to use the dataset as an input in an exterior program that will generate a complex figure/plot.

So the OUTPUT has to be in a specific order. Since I haven't run into any SAS procedures that are picky I have to assume that you would be talking about a text file or such in a specific order.

I have done a lot of that, to include specifying rows and columns on a page. Data step and put statements work for that.

Or having the variable names on a VAR statement in Proc Print.

A multitude of like named, or grouped variables could be done with a data step to create long string value that you then stuff into a macro variable.

data example;
   length longstring $ 5000;
   varlist= "thisvar thatvar othervar";
   /* suppose we want to creat 15 grouped variable names
      space delimited in a long string*/
   do i= 1 to 15; /*the number of groups*/
      do j=1 to countw(varlist);
         longstring = catx(' ',longstring,cats(scan(varlist,j),i));
      end;
   end;
   call symputx('myvarlist',strip(longstring));
run;

%put the longstring is: &myvarlist.;

Then you could use the macro variable myvarlist such as in

Proc print data=somedataset;
   var &myvarlist. ;
run;

or any other place you want that list of variables.