topic Re: how to use proc sort dyanmically in SAS Programming

how to use proc sort dyanmically

hexx18 — Tue, 28 Jun 2016 20:22:41 GMT

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 ?

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

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 ?

Re: how to use proc sort dyanmically

Reeza — Tue, 28 Jun 2016 20:27:34 GMT

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;

Re: how to use proc sort dyanmically

PGStats — Tue, 28 Jun 2016 20:36:16 GMT

Only works for the first column:

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

Re: how to use proc sort dyanmically

hexx18 — Tue, 28 Jun 2016 20:39:23 GMT

what if i want to order by only first column ?

Re: how to use proc sort dyanmically

PGStats — Tue, 28 Jun 2016 20:44:49 GMT

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

Re: how to use proc sort dyanmically

hexx18 — Tue, 28 Jun 2016 21:06:19 GMT

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 ?

Re: how to use proc sort dyanmically

Reeza — Tue, 28 Jun 2016 22:41:17 GMT

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;

Re: how to use proc sort dyanmically

ChrisNZ — Tue, 28 Jun 2016 23:42:17 GMT

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

Re: how to use proc sort dyanmically

Kurt_Bremser — Wed, 29 Jun 2016 08:57:30 GMT

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.

Re: how to use proc sort dyanmically

hexx18 — Wed, 29 Jun 2016 14:56:23 GMT

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

Re: how to use proc sort dyanmically

hexx18 — Wed, 29 Jun 2016 15:11:08 GMT

Thanks a lot it worked