DATA Step, Macro, Functions and more

how to use proc sort dyanmically

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

how to use proc sort dyanmically

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

NUMBERITEMSCODES
22222232222
7141111545454
315151515454545
18888888

484888

 

 

b2

 

IDITEMSCODES
21151515746467
6155154748784
57884878234454
175157874864487

 

 

%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 ?


Accepted Solutions
Solution
‎06-29-2016 11:10 AM
Super User
Posts: 6,936

Re: how to use proc sort dyanmically

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 17,814

Re: how to use proc sort dyanmically

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;

Contributor
Posts: 22

Re: how to use proc sort dyanmically

 

 

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 ?

Super User
Posts: 17,814

Re: how to use proc sort dyanmically

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;
PROC Star
Posts: 1,561

Re: how to use proc sort dyanmically

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

 

 

Contributor
Posts: 22

Re: how to use proc sort dyanmically

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

Respected Advisor
Posts: 4,644

Re: how to use proc sort dyanmically

Only works for the first column:

 

proc sort data=sashelp.class out=sortedClass; 
by _all_; 
run;
PG
Contributor
Posts: 22

Re: how to use proc sort dyanmically

what if i want to order by only first column ?

Respected Advisor
Posts: 4,644

Re: how to use proc sort dyanmically

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

PG
Solution
‎06-29-2016 11:10 AM
Super User
Posts: 6,936

Re: how to use proc sort dyanmically

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 22

Re: how to use proc sort dyanmically

Thanks a lot  it worked

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 425 views
  • 5 likes
  • 5 in conversation