sorting of alpha_numeric like contents of book

[abhaykumar]

I want to sort this as it is in book contents page, data is as follows :

data assignment2;
infile cards;
input var $ 30.;
cards;
Table_1_Contents
Table_2_Introduction
Table_3_History1
Table_4_History2
Table_5_History3
Table_6_History4
Table_7_History5
Table_8_History6
Table_8_History7
Table_10_History8
Table_11_History9
Table_12_History10
Table_13_Appendix
Table_1A_Foreword
Table_1B_Preface
Table_1C_Acknowledgments
Table_13A_AppendixA
Table_13B_AppendixB
Table_13C_AppendixC
Table_2A_Prologue
Table_14_Notes
Table_15_Glossary
Table_16_References
Table_17_Index
;

Expected output should be:

Table_1_Contents

Table_1A_

Table_1B_

Table_1C_

Table_2_

Table_2A_

Table_2B_

Table_2C_

Table_3_

Table_2A_

......... like that ;

I hope you understand what I want.

Thanks you.

[LinusH]

SAS sort char variables by position in the string, and I don't see that this can be done without manipulating the data.

I would insert a char between the chapter no and the specific section chars (A, B and C). it needs to be a char with an ASCII value lower than A and the numbers, so space seems like a natural choice.

[abhaykumar]

If you can manipulate data then how it can be done?
Can you please write code?

[ballardw]

I am going to assume that you are using a version of SAS 9.2 or later. I am not going to write code as this looks like a school assignment and you need to learn but some hints:

Proc Sort has an option SORTSEQ that allows you to specify some rules to sort with. One of the rules is Linguistic which supports an option NUMERIC_COLLATION.

[abhaykumar]

Thank you sir for you help.
and NO sir this is NOT any college or school assignment, I am self learning SAS, try to solve any problem I get from anywhere.

[arodriguez]

Hi,
To solve this the only thing that come to my mind is to create a new variable with the second word deleting all categorical information (could be done with a compress(xxxx,'0123456789','K')) and the same with other variable keeping only the caregorical info (compress deleting the 'K' option), then you could do a sort by this two new vars.

[RW9 · Accepted solution]

You need extra columns for the sort:

data assignment2;
  infile cards;
  input var $ 30.;
cards;
Table_1_Contents
Table_2_Introduction
Table_3_History1
Table_4_History2
Table_5_History3
Table_6_History4
Table_7_History5
Table_8_History6
Table_8_History7
Table_10_History8
Table_11_History9
Table_12_History10
Table_13_Appendix
Table_1A_Foreword
Table_1B_Preface
Table_1C_Acknowledgments
Table_13A_AppendixA
Table_13B_AppendixB
Table_13C_AppendixC
Table_2A_Prologue
Table_14_Notes
Table_15_Glossary
Table_16_References
Table_17_Index
;
run;
data want;
  set assignment2;
  main=input(compress(scan(var,2,"_"),,"KD"),best.);
  sec=compress(scan(var,2,"_"),,"KA");
run;
proc sort data=want;
  by main sec;
run;

However you can also do (same test data):

proc sql;
  create table WANT as
  select  *
  from    ASSIGNMENT2
  order by input(compress(scan(var,2,"_"),,"KD"),best.),
            compress(scan(var,2,"_"),,"KA");
quit;

[abhaykumar]

Thank You sir, I got the explanation.
But I am still confused about the third argument of compress function "KD" and "KA". Can you please elaborate on that because my i have not read anything about three argument in the compress function.

[RW9]

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000212246.htm

Basically the third parameter is for options.  So you could put A in ther to add all alphabetic characters to the list of complress characters.  In my case I use K to reverse the logic of the function, instead of dropping those in the list I keep them, so you could look at like compress the text Keeping Digits.

[Reeza]

Since no ones mentioned it, it may not apply in this situation but you can sometimes use the SORTSEQ option in PROC SORT to sort character variables that have numbers in it correctly. 

http://support.sas.com/documentation/cdl/en/proc/68954/HTML/default/viewer.htm#p02bhn81rn4u64n1b6l00...