topic Keeping output columns in order with proc transpose missing values in SAS Procedures

Keeping output columns in order with proc transpose missing values

Walternate — Wed, 22 Jul 2015 16:06:58 GMT

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.

Re: Keeping output columns in order with proc transpose missing values

Reeza — Wed, 22 Jul 2015 16:15:00 GMT

Try Art's macro:

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

Re: Keeping output columns in order with proc transpose missing values

data_null__ — Wed, 22 Jul 2015 16:54:10 GMT

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_

Re: Keeping output columns in order with proc transpose missing values

Ksharp — Fri, 24 Jul 2015 14:25:51 GMT

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;