Keeping output columns in order with proc transpose missing values

[Walternate]

Hi,

I have a dataset that is at the person/event level--each row represents one event for one person. It has ID variable, a target date, a variable I created to serve as the column heads after transposing, a month count, and a count variable. The month count indicates the number of months either before (in which case it will be negative) or after (positive) the target date that an event occurred. The variable I created corresponds with the month count variable:

ID     Target_date     Mth_count     Transpose_var          Count

1        01/2010                -5                  5_pre                       5

1        01/2010                -3                  3_pre                       7

1        01/2010                 2                   2_post                    13

1        01/2010                 7                   7_post                    27

I have transposed the data such that the month counts are now columns (I kept the target date):

proc transpose data=have out=want prefix=mth_;

by ID target_date;

var count;

id transpose_var;

run;

This is the resulting dataset:

ID     Target_date     mth_5_pre     mth_3_pre     mth_2_post     mth_7_post    

1        01/2010                 5                  7                       13               27

The problem is that I need all the resulting month columns to be in order (ie, mth_10_pre, mth_9_pre...mth_0, mth_1_post, mth_2_post...mth_10_post). I know that the order is off because Person 1 has missing values for the other months, but I'm hoping there's a way to get SAS to output the columns in the order I want them.

Any help is much appreciated.

[Reeza]

Try Art's macro:

A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity

[data_null__]

Create some observations with ID=missing that establish the desired variable order, place these records at the beginning of the data, (perhaps using a view).  Then remove that ID with a WHERE data set option on the OUT= data specification.  Easy.

proc format;
   picture mth
      -10--1='009_pre'
      0='009'
      1-10='009_post'
   ;
   run;
data mth;
   input ID  Target_date $  Mth count;
   cards;
1        01/2010   -5   5
1        01/2010   -3   7
1        01/2010    2   13
1        01/2010    7   27
;;;;
   run;
proc print;
   run;
data mthv / view=mthv;
   if 0 then set mth;
   if _n_ eq 1 then do mth=-10 to 10;
      output;
      end;
   set mth;
   output;
   run;
proc transpose data=mthv out=want(drop=_name_ where=(not missing(id))) prefix=mth_;
   by ID target_date;
   var count;
   id mth;
   format mth mth.;
   run;
proc print;
   run;

Message was edited by: data _null_

[Ksharp]

SQL reorder variables' name .

Code: Program

data have;
input   ID   Target_date $   Mth_count   Transpose_var   $   Count;
   cards; 
1 01/2010 -5 5_pre 5
1 01/2010 -3 3_pre 7
1 01/2010 2 2_post 13
1 01/2010 7 7_post 27
;;;;
   run; 
proc transpose data=have out=temp prefix=mth_;
by ID target_date;
var count;
id transpose_var;
run;
proc sql;
 select name into : list separated by ' '
  from dictionary.columns
   where libname='WORK' and memname='TEMP' and name like 'mth%'
   order by input(compress(name,,'kd'),best32.);
quit;
data want;
 retain ID target_date &list;
 set temp;
run;