topic Re: Proc Transpose - order ID in proper sequence in SAS Programming

Proc Transpose - order ID in proper sequence

KrunalPatel — Thu, 23 Aug 2012 10:51:30 GMT

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:

Code	Mth	Pay
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

Re: Proc Transpose - order ID in proper sequence

Keith — Thu, 23 Aug 2012 13:05:35 GMT

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=_:) 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;

Re: Proc Transpose - order ID in proper sequence

art297 — Thu, 23 Aug 2012 13:07:57 GMT

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"></style><head><title>LISTSERV at the University of Georgia ))

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=_:) 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;

Re: Proc Transpose - order ID in proper sequence

PGStats — Thu, 23 Aug 2012 13:30:44 GMT

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;

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

Re: Proc Transpose - order ID in proper sequence

KrunalPatel — Thu, 30 Aug 2012 10:58:28 GMT

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