topic Re: RE: PROC TRANSPOSE with correct columns order in SAS Programming

RE: PROC TRANSPOSE with correct columns order

twildone — Wed, 13 Apr 2016 13:55:06 GMT

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;

Re: RE: PROC TRANSPOSE with correct columns order

RW9 — Wed, 13 Apr 2016 14:15:48 GMT

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;

Re: RE: PROC TRANSPOSE with correct columns order

buechler66 — Wed, 13 Apr 2016 14:26:08 GMT

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.

Re: RE: PROC TRANSPOSE with correct columns order

Ksharp — Thu, 14 Apr 2016 02:24:21 GMT

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;