DATA Step, Macro, Functions and more

Sorting issue

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

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
Super User
Posts: 7,977

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,977

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

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

Solution
‎05-29-2014 05:33 AM
Super User
Super User
Posts: 7,977

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.

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

Discussion stats
  • 3 replies
  • 211 views
  • 3 likes
  • 2 in conversation