Sorting issue

[avinesh]

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

[RW9]

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;

[avinesh]

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

Papad 250g

Papad 500g

;

run;

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

[RW9 · Accepted solution]

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.