BookmarkSubscribeRSS Feed
mrinmoy
Obsidian | Level 7
 how to sort character/alphabetical data in logical order (without sorting uppercase letters and lowercase letters separately)?
 
I tried to use SORTSEQ = LINGUISTIC option, but I get the error in the screenshot below.
7 REPLIES 7
Reeza
Super User

No screenshot is included. Please include your code/log and what version of SAS you're using.

 

It seems this should work as of SAS 9.2+

http://support.sas.com/kb/31/369.html

mrinmoy
Obsidian | Level 7

I have a month variable and i want it in a month order like jan fab march etc

 

I tried to use SORTSEQ = LINGUISTIC option, but I get the error in the screenshot below.

sas: 9.2

Error: Translation table for sort sequence LINGUIST no found 

 

 

Reeza
Super User

Proc sort won't sort it the way you want. You'll need to create a new variable that has the month in a format that will sort according to a calendar. 

 

 

SASKiwi
PROC Star

Convert your month variable to a SAS date and sort the SAS date, then your data will be in correct date order.

Kurt_Bremser
Super User

And if you need only months, not complete dates (eg because you want to work with months across years), then store the months as numbers 1-12 and apply a custom format to show month names.

DanielSantos
Barite | Level 11

Hi.

 

You could use SQL, which is more flexible to order data the way you want it.

 

Just use an available informat or create one that will create the sequence needed, for example:

 

 proc format;
         invalue MNTH (upcase)
                     'JAN'=1  'FEB'=2  'MAR'=3
                     'APR'=4  'MAY'=5  'JUN'=6
                     'JUL'=7  'AUG'=8  'SEP'=9
                     'OCT'=10 'NOV'=11 'DEC'=12
                      other=-1;
run; * create custom informat;

proc sql;
create table WANT as
select *
from HAVE
order by input(VAR,MNTH.); * order using the informat;
quit;

 

ORDER BY will accept functions and you can combine more than one variable.

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

 

mkeintz
PROC Star

Given the fixed and relatively limited number of values of the sort variable, this is a case where your knowledge of the data might avoid the need for proc sort, possibly saving lots of intermediate disk input/output activity:

 

 

data want;
  set have (where=(month='JAN'))
      have (where=(month='FEB'))
      have (where=(month='MAR'))
      have (where=(month='APR'))
      have (where=(month='MAY'))
      have (where=(month='JUN'))
      have (where=(month='JUL'))
      have (where=(month='AUG'))
      have (where=(month='SEP'))
      have (where=(month='OCT'))
      have (where=(month='NOV'))
      have (where=(month='DEC'))
	open=defer;
run;

 

 

 

The "open=defer" option tells SAS not to allocate memory to simultaneously creating input buffers for all 12 SET datasets.  Just use the same buffer 12 times, in sequence.   You can get away with this option if (1) all the buffers would accomodate the same variables and (2) you do not use a BY statement (which would require SAS to determine record order by comparing inputs from all sources).

 

Of course, the downside of this approach is that, even though no utility/intermediate files are created (i.e. no excess disk output), it does read dataset HAVE 12 times.   There is a tradeoff between number of sort levels and sort-utility file needs.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 1050 views
  • 6 likes
  • 6 in conversation