Solved
Contributor
Posts: 29

# Sorting issue

Hello ,

I am facing sorting issue in my data.So I need a solution for that.

the sample data is

data a;

infile datalines dlm=',';

input name \$ 15.;

cards;

chilli 10g

chilli 100g

chilli 15g

chilli 200g

chilli 150g

chilli 1kg

chilli 250g

chilli 500g

;

run;

proc sort data=a;

by name;

run;

My required output is:

chilli 10g

chilli 15g

chilli 100g

chilli 150g

chilli 200g

chilli 250g

chilli 500g

chilli 1kg

Accepted Solutions
Solution
‎05-29-2014 05:33 AM
Super User
Posts: 9,599

## Re: Sorting issue

Assuming its just one word at the beginning you could change to:

proc sql;

create table WANT (drop=WEIGHT) as

select  NAME,

case  when index(NAME,"kg")>0 then input(tranwrd(substr(NAME,1,index(NAME," ")),"kg",""),best.) * 1000

else input(tranwrd(substr(NAME,1,index(NAME," ")),"g",""),best.) end as WEIGHT

from    A

order by WEIGHT;

quit;

You just need to modify the input part to manipulate the string to get only the number part, that depends on you data.

All Replies
Super User
Posts: 9,599

## Re: Sorting issue

You need to split the numeric part out from the text, multiply it per the unit, then sort by that value:

proc sql;

create table WANT (drop=WEIGHT) as

select  NAME,

case  when index(NAME,"kg")>0 then input(tranwrd(tranwrd(NAME,"chilli",""),"kg",""),best.) * 1000

else input(tranwrd(tranwrd(NAME,"chilli",""),"g",""),best.) end as WEIGHT

from    A

order by WEIGHT;

quit;

Contributor
Posts: 29

## Re: Sorting issue

HI RW9

In the sample data i mentioned only chilli But in real time data im having many products with different names for example

data a;

infile datalines dlm=',';

input name \$ 15.;

cards;

chilli 10g

chilli 100g

chilli 15g

chilli 200g

chilli 150g

chilli 1kg

chilli 250g

chilli 500g

Tumeric 10g

Tumeric 100g

Tumeric 15g

Masala 200g

Masala 150g

Masala 1kg

;

run;

If i get a solution for this,it will be more helpful for me can you help me out thanks in advance

Solution
‎05-29-2014 05:33 AM
Super User
Posts: 9,599

## Re: Sorting issue

Assuming its just one word at the beginning you could change to:

proc sql;

create table WANT (drop=WEIGHT) as

select  NAME,

case  when index(NAME,"kg")>0 then input(tranwrd(substr(NAME,1,index(NAME," ")),"kg",""),best.) * 1000

else input(tranwrd(substr(NAME,1,index(NAME," ")),"g",""),best.) end as WEIGHT

from    A

order by WEIGHT;

quit;

You just need to modify the input part to manipulate the string to get only the number part, that depends on you data.

🔒 This topic is solved and locked.