RE: PROC TRANSPOSE with correct columns order

[twildone]

Hi....I am trying to get the output to order the columns that have been transposed. It seems the problem that I am having is that because not all ID's have data for each YrMonth, and since the data is sorted by ID and then YrMonth, the output and columns begin withthe results from the first ID. Any suggestions how I can remedy this so that the first transposed column is the first ordered YrMonth....Thanks.

PROC SORT DATA=NEW1;
BY NAME ID YRMONTH;
RUN;

PROC TRANSPOSE DATA=NEW1 OUT=WANT;
BY NAME ID;
ID YRMONTH;
VAR COST;
RUN;

[RW9]

Hi,

Please don't code all in uppercase - it is very difficult to read.  Also, providing test data in the form of a datastep will yield you tested code.  So what your saying is you end up with something like:

ID    _201501  _201506  _201504...

Yes?

If so then maybe you could create a shell dataset, and then set the transposed data back to that -

data new1;
  name="abc"; id=1; yrmonth=201501; cost=34; output;
  name="abc"; id=1; yrmonth=201505; cost=.; output;
  name="abc"; id=1; yrmonth=201506; cost=54; output;
run;
proc sql; 
  create table TEMP (_201501 num,_201505 num,_201506 num);
run;
proc sort data=new1;
  by name id yrmonth;
run;
proc transpose data=new1 out=want;
  by name id;
  id yrmonth;
  var cost;
run;
data want;
  set temp want;
run;

[buechler66]

Forum genius Tom helped me with this yesterday...

One method is to create a dummy set of records that has all of the dates in the order that you want so that PROC TRANSPOSE will see those dates first. Right now it is seeing them in the order that they appear after the data has first been sorted.  You can then drop that resulting row from the output dataset.

proc sort data=FinalData (keep=ad_dt) out=dates nodupkey;
  by ad_dt;
run;

data both ;
  set dates finalData ;
run;

proc transpose data=both out=Want(drop=_name_ where=(rule_nm ne ' '));
  by rule_nm rule_order;
  id ad_dt_char;
  var n;
run;

Proc transpose creates the columns as it sees new values of the ID variable. So by putting at least one example of every possible date in the first BY group (and in the right order) then it creates the columns in the right order.  Then the WHERE= dataset option deletes the extra group from the output.  Consider a  simple example.

data have ;
   input gender $ year count @@;
cards;F 2003 10 F 2005 11 M 2002 12 M 2003 13;;;;
proc transpose data=have out=want ;
  by gender ;
  id year ;
  var count;
run;

This will result in columns GENDER _2003 _2005 _2002.

But if I add an extra set of records that before the F group that has YEAR in the order 2002,2003,2005 then the columns in the output dataset will be created in that order.

[Ksharp · Accepted solution]

you can always change the order of variables after proc transpose .

Use SQL select the right order variable name and Use RETAIN.

proc sql;

 select distinct cats('_',year) into : list separated by ' ' 

 from have 

   order by year;

quit;

..........

proc transpose out=want;.........

data final;

 retain Gender &list Total;

 set want;

run;