in the output, the values for variable year would become variables themselves but year2004 is before year2003. How do I put year2003 before year2004. What modifications do I need to make to my code in order to do this?
I'm not sure if there is any option that can do what you want, but you can always reorder the variables after having run proc transpose. For example, the following will do just that if run after the statements shown in your example:
proc sql noprint;
select strip(name) into :vars separated by ' '
where libname="WORK" and memname="TEST2"
order by 1
input sid score year;
1 89 2001
2 88 2001
1 92 2002
2 98 2003
1 99 2004
2 89 2004
proc sql ;
create table temp as
from (select distinct sid from test),(select distinct year from test)
create table op as
from temp left join test on temp.sid = test.sid and temp.year = test.year
proc transpose data=op out=want prefix=year;