topic Re: Sort by user defined criteria in SAS Programming

Sort by user defined criteria

Ronein — Sun, 30 Jan 2022 07:48:54 GMT

Hello

I want to sort by user defined order.

Why this code is not working?

I expect to get results


 Data have;
 input appidentity ID Var $ value Supplement; 
 cards;
888888 111 Inter . 100
888888 111 Age 50 200
888888 111 Wealth 5 300
888888 111 Educ 30 200
888888 111 Loan 65 500
888888 111 Cycle 120 100
888888 111 Total . 1400
888888 222 Inter . 200 300
888888 222 Age 40 200
888888 222 Wealth 300 500
888888 222 Educ 5 500
888888 222 Loan 40 200
888888 222 Cycle 10 100
888888 222 Total . 1800
999999 777 Inter . 100
999999 777 Age 60 200
999999 777 Wealth 500 700
999999 777 Educ 8 200
999999 777 Loan 0 300
999999 777 Cycle 300 1500
999999 777 Total . 3000
;
RUN;
 
Data Have2;
SET have;
appidentity_=put(appidentity,best.);
ID_=put(ID,best.);
RUN;

proc sql;
create table Want as
select *
from Have2
order by whichc(appidentity_,
                ID_,
				'Inter',
				'Educ',
				'Loan',
				'Cycle',
				'Wealth',
				'Age',
				'Total');
quit;

Re: Sort by user defined criteria

japelin — Sun, 30 Jan 2022 08:29:22 GMT

how about this

proc sql;
create table Want as
select *
from Have
order by appidentity
        ,ID
        ,whichc(VAR,
		       'Inter',
		       'Educ',
		       'Loan',
		       'Cycle',
		       'Wealth',
		       'Age',
		       'Total');
quit;

check whichc

Re: Sort by user defined criteria

Ksharp — Sun, 30 Jan 2022 08:58:20 GMT

 Data have;
 input appidentity ID Var $ value Supplement; 
 cards;
888888 111 Inter . 100
888888 111 Age 50 200
888888 111 Wealth 5 300
888888 111 Educ 30 200
888888 111 Loan 65 500
888888 111 Cycle 120 100
888888 111 Total . 1400
888888 222 Inter . 200 300
888888 222 Age 40 200
888888 222 Wealth 300 500
888888 222 Educ 5 500
888888 222 Loan 40 200
888888 222 Cycle 10 100
888888 222 Total . 1800
999999 777 Inter . 100
999999 777 Age 60 200
999999 777 Wealth 500 700
999999 777 Educ 8 200
999999 777 Loan 0 300
999999 777 Cycle 300 1500
999999 777 Total . 3000
;
RUN;


proc format;
invalue fmt
'Inter'=1
'Educ'=2
'Loan'=3
'Cycle'=4
'Wealth'=5
'Age'=6
'Total'=7
;
run;

proc sql;
create table want as
select *
 from have 
  order by appidentity ,ID ,input(var,fmt.);
quit;

Re: Sort by user defined criteria

PeterClemmensen — Sun, 30 Jan 2022 11:49:41 GMT

@Ronein, I see that you adapted my technique from the post here.

Please mark that as closed before you open a new thread 🙂