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
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 | Table1:34 |
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;
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=_:) 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;
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.
Thanks everyone. let me try the optoins mention by all of you.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.