topic Re: Sorting issue in SAS Programming

Sorting issue

avinesh — Thu, 29 May 2014 09:02:09 GMT

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

Re: Sorting issue

RW9 — Thu, 29 May 2014 09:15:56 GMT

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;

Re: Sorting issue

avinesh — Thu, 29 May 2014 09:27:57 GMT

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

Re: Sorting issue

RW9 — Thu, 29 May 2014 09:33:10 GMT

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.