BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avinesh
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Fluorite | Level 6

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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