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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.