topic Re: proc transpose with columns out of order in SAS Procedures

proc transpose with columns out of order

Steelers_In_DC — Tue, 24 Mar 2015 13:33:01 GMT

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

Re: proc transpose with columns out of order

Haikuo — Tue, 24 Mar 2015 13:58:34 GMT

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

Re: proc transpose with columns out of order

Vish33 — Tue, 24 Mar 2015 14:04:38 GMT

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

Re: proc transpose with columns out of order

RW9 — Tue, 24 Mar 2015 14:12:07 GMT

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.

Re: proc transpose with columns out of order

Tom — Tue, 24 Mar 2015 14:17:40 GMT

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;

Re: proc transpose with columns out of order

data_null__ — Tue, 24 Mar 2015 14:45:49 GMT

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.