02-03-2017 12:07 AM
02-03-2017 12:40 AM
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.
Error: Translation table for sort sequence LINGUIST no found
02-03-2017 04:11 AM
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.
02-06-2017 10:27 AM - edited 02-06-2017 10:28 AM
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
02-06-2017 11:17 AM
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.