proc transpose with columns out of order

[Steelers_In_DC]

I am using the below procs which give me close to the desired result.  The first ID does not have the full range of dates I am displaying so the final transpose doesn't show it in order.  It starts with the first date that the first ID has.  I tried to work around moving the ID's around but I get an error because I have the ID in the by statement.  Is there any work around for this?  I'm going to end up cutting/pasting cells in excel which I'm not a fan of.  What I have is a column starting with 01/31/2014cure_count, when the range of dates starts with 08/31/2013.  I would like to have the range of dates in order regardless of the ID  Any ideas will be appreciated.

proc transpose data=r4_display_prep out=r4_test_transpose(drop=_label_);by power_id record_date;

var cure_count PD_CD max_pass_date;run;

proc transpose data=r4_test_transpose out=r4_final_transpose(drop=_name_);by power_id;id record_date _name_;var col1;run;

Thanks,

Mark

[Haikuo]

I am not sure if you can do it in one-step Proc Transpose, but for this kind of 'variable order' rearrangement, you can reliably do it using Retain statement by an additional date step.

data new_order;

retain var1 var2.....varN /*this is the order you want to see*/;

set  old;

run;

Good luck,

Haikuo

[Vish33]

Can you provide us a sample data which you are trying to transpose, then i can able to check the code.

[RW9 · Accepted solution]

Hi,

You will find many posts on this forum which deal with this concept of putting date information as columns headers.  I would strongly advise against such a thing.  The reason is that you will lose a lot of functionality built into SAS, and there are other ways to get what you want.  Now, what I would do is this:

Have a datastep which goes through your data and assigns a column number to the dates, i.e. in the order they should appear in the output.  Then transpose or alternatively array process your data to get transposed table, and assign the LABEL of the variable to be the date in question.  Hence you would end up with a dataset with structure:

...

COL1     label="01Mar2014"

COL2     label="02Mar2014"

...

This way you can refer to the columns using COLx, e.g. col1-colx.  You then have full array processing functionality, plus the information in the label can be exported to Excel in the correct order.  Try to avoid putting data elements as column descriptors, i.e. separate table metadata from your data.

[Tom]

You can add an extra set of records with a dummy value for POWER_ID and all values of the date variable and then delete it from the resulting data set.

Note that you should use YYMMDD format instead of MMDDYY or DDMMYY for your date strings if you want them to sort more naturally as labels or variable names.

proc sort data=r4_display_prep(keep=record_date) nodupkey out=dummy ;

  by record_date ;

run;

data dummy2 ;

  set dummy r4_display_prep ;

run;

proc transpose data=dummy2 out=r4_test_transpose;

  by power_id record_date;

  var cure_count PD_CD max_pass_date;

run;

proc transpose data=r4_test_transpose

  out=r4_final_transpose(drop=_name_ where=(not missing(power_id));

  by power_id;

  id record_date _name_;

  var col1;

run;

[data_null__]

Tom wrote:

You can add an extra set of records with a dummy value for POWER_ID and all values of the date variable and then delete it from the resulting data set.
Note that you should use YYMMDD format instead of MMDDYY or DDMMYY for your date strings if you want them to sort more naturally as labels or variable names.

proc sort data=r4_display_prep(keep=record_date) nodupkey out=dummy ;
  by record_date ;
run;
data dummy2 ;
  set dummy r4_display_prep ;
run;

proc transpose data=dummy2 out=r4_test_transpose;
  by power_id record_date;
  var cure_count PD_CD max_pass_date;
run;


proc transpose data=r4_test_transpose 
  out=r4_final_transpose(drop=_name_ where=(not missing(power_id));
  by power_id;
  id record_date _name_;
  var col1;
run;

This is the correct correct answer.