Proc Transpose - order ID in proper sequence

Reply
Contributor
Posts: 21

Proc Transpose - order ID in proper sequence

Hi All,

Anybody can please guide me how can I sequence the ID in proper order.

e.g.

I want to traspose below "Table1":

I want to transpose as:

Proc Transpose data= Table1 out=Table2;

by Code;

id Mth;

var Pay;

run;

while doing this i am not getting my ID in sequence

                                                            Table1:
CodeMthPay
ABC112
DEF212
XYZ134
ABC223
DEF145
XYZ265
ABC56
ABC412
ABC334
Regular Contributor
Posts: 151

Re: Proc Transpose - order ID in proper sequence

Posted in reply to KrunalPatel

The problem is that the order of the ID variables is determined by the order they appear in the input dataset.  You could solve the problem in your example data just by sorting table1 by Code and Mth, however this won't necessarily work in your real data.  There is no way I know of where you can specify the order of the variables within PROC TRANSPOSE, so one of your options is to reorder the output dataset afterwards.  Here's one way of doing that.

data table1;

input Code $ Mth Pay;

datalines;

ABC 1 12

DEF 2 12

XYZ 1 34

ABC 2 23

DEF 1 45

XYZ 2 65

ABC 5 6

ABC 4 12

ABC 3 34

;

run;

proc sort data=table1;

by code;

run;

Proc Transpose data=table1 out=table2 (drop=_Smiley Happy prefix=Mth;

by Code;

id Mth;

var Pay;

run;

proc sql noprint;

select name into :vars separated by ','

from dictionary.columns

where libname='WORK' and upcase(memname)='TABLE2' and upcase(name) eqt 'MTH'

order by name;

create table table2 as select

code,

&vars.

from table2;

quit;

PROC Star
Posts: 7,471

Re: Proc Transpose - order ID in proper sequence

Posted in reply to KrunalPatel

Not sure exactly what you want to end up with but, hopefully, the following will at least give you an idea.  p.s. the method shown below is from an old sas-l post by datanull (see: SAS-L archives -- October 2009, week 1 (#177)</title><style type="text/css"><!--BODY { font-family: ...

data table1;

  input Code $ Mth Pay;

  cards;

ABC 1 12

DEF 2 12

XYZ 3 34

ABC 2 23

DEF 1 45

XYZ 2 65

ABC 5 6

ABC 4 12

ABC 3 34

;

proc summary data=table1;

   output out=dateinfo(drop=_Smiley Happy min(mth)=min max(mth)=max;

run;

data table2;

   if 0 then set table1;

   set dateinfo;

   do mth = min to max;

      output;

      end;

   do until(eof);

      set table1 end=eof;

      output;

      end;

   stop;

run;

proc sort data=table2;

  by code;

run;

proc transpose data=table2 out=want(where=(not missing(code))) prefix=mth;

   by code;

   var pay;

   id mth;

   idlabel mth;

run;

Respected Advisor
Posts: 4,920

Re: Proc Transpose - order ID in proper sequence

Posted in reply to KrunalPatel

Same idea, using SQL :

data table1;
     input Code $ Mth Pay;
     cards;
ABC 1 12
DEF 2 12
XYZ 3 34
ABC 2 23
DEF 1 45
XYZ 2 65
ABC 5 6
ABC 4 12
ABC 3 34
;


proc sql;
create table tableX as
select *
from (select unique a.code, b.mth from table1 as a, table1 as b)
     natural left join table1
order by code, mth;
quit;

proc transpose data=tableX out=want(drop=_NAME_) prefix=mth;
     by code;
     var pay;
     id mth;
     idlabel mth;
run;

PG

Message was edited by: PG - simplified code, a bit.

PG
Contributor
Posts: 21

Re: Proc Transpose - order ID in proper sequence

Posted in reply to KrunalPatel

Thanks everyone. let me try the optoins mention by all of you.

Ask a Question
Discussion stats
  • 4 replies
  • 396 views
  • 0 likes
  • 4 in conversation