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

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.

 

 

1 ACCEPTED SOLUTION

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

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;

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

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.

Data never sleeps
abhaykumar
Obsidian | Level 7
If you can manipulate data then how it can be done?
Can you please write code?
ballardw
Super User

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
Obsidian | Level 7
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
Lapis Lazuli | Level 10
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
Diamond | Level 26 RW9
Diamond | Level 26

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

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
Super User

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...

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 9 replies
  • 1831 views
  • 1 like
  • 6 in conversation