how to use proc sort dyanmically

[hexx18]

Hi ,

I have two datasets B1 and B2 and i want to sort both datasets by first column can i do it dyanmically without indicating the name of teh column ?

B1

NUMBER	ITEMS	CODES
2	222223	2222
7	141111	545454
3	15151515	454545
1	8888888	484888

b2

ID	ITEMS	CODES
2	1151515	746467
6	1551547	48784
5	7884878	234454
1	7515787	4864487

%macro xc;

proc sort data=B_&i out= c_&i;

by ;

run;

%mend;

%xc;

i want to order by first column for B1 and B2 . Can anyone suggest me what should i do ?

[Reeza]

Not via proc sort but Proc SQL does allow you to reference columns using a column number. 

Proc sql;

create table class as

select *

from sashelp.class

order by 1;

quit;

[hexx18]

i did

proc sql;

create table c_&i. as

  select * from b_&i.

order by 1;

quit;

but its not working its because the first column is character ?

[Reeza]

No, the first column can be character. What does your log say, please include the error as well

And does this work?

proc sql;
create table class as
select *
from sashelp.class
order by 1;
quit;

[ChrisNZ]

If the sort order is incorrect because you have numbers in strings and you want to sort by the number in the string, do this:

data HAVE;
  x='10'; output;
  x='5';  output;
run;

proc sort sortseq=linguistic(numeric_collation=on);
  by _ALL_;
run;

proc print; 
run;

Obs	x
1	5
2	10

[hexx18]

its not working probably my ID nad number coloumns are character is there a way that i can change ID and number coloumns from both B1 and B2 datasets to numberic while its getting imported ? so that when there is a sas dataset i will have id and number coloumns numeric

Thanks

[PGStats]

Only works for the first column:

proc sort data=sashelp.class out=sortedClass; 
by _all_; 
run;

[hexx18]

what if i want to order by only first column ?

[PGStats]

A dataset ordered by _ALL_ is ordered by the first column. In any case, you are better off with SQL, as @Reeza suggested.

[Kurt_Bremser · Accepted solution]

How about that:

%macro xc(i);

data _null_;
set sashelp.vcolumn;
where upcase(libname) = 'WORK' and upcase(memname) = upcase("B_&i");
if _n_ = 1
then do;
  call symput('varname',name);
  stop;
end;
run;

proc sort data=B_&i out=c_&i;
by &varname;
run;

%mend;

%xc(1);

Not tested, of course. Therefore no warranty or liability.

[hexx18]

Thanks a lot  it worked