Reordering a character variable

[Whitlea]

I would like to reorder my month variable so that instead of months 1-12 least to greatest, I would like it in the order from June -July (07,08,09,10,11,12,01,02,03,04,05,06). I tried using proc format to reorder the variable, but it did not order the months correctly when I applied that format in a proc tabulate.   The month variable is a character variable ($2).  Thanks for the help!

proc format;
value $months
1='07'
2='08'
3='09'
4='10'
5='11'
6='12'
7='01'
8='02'
9='03'
10'04'
11='05'
12='06';
run;

Proc tabulate data =work.pt format=dollar12.;
class  Month;
format Month $months.;
var pay ;
table pay=' '* Month=' ' ;
run;

[mkeintz]

By using a leading $, you have correctly recognized that you need to create a character variable format.  But you provided a list of numeric values to be formatted.  You need to provide a list of character values to be formatted, as below:

data have;
 input month $2.    sales  @@;
datalines;
01 101   02 102   03 103   04 104   05 105   06 106
07 107   08 108   09 109   10 110   11 111   12 111
run;
proc format;
  Value $fiscal_mnth   /*Fiscal year from July through June */
'01'='07'   '02'='08'   '03'='09'   '04'='10'   '05'='11'   '06'='12'
'07'='01'   '08'='02'   '09'='03'   '10'='04'   '11'='05'   '12'='06'
;
run;
proc tabulate data=have  order=formated;
  class month;
  format month $fiscal_mnth2.;
  var sales;
  tables month, sum*sales;
run;

[ballardw · Accepted solution]

First make sure your format definition is correct. You are missing an = for the 10. Really a good idea to use quotes on both sides for a character format just to avoid confusion.

Try:

proc sort data=pt sortseq=linguistic(numeric_collation=on);
   by month;
run;
Proc tabulate data =work.pt format=dollar12.;
class  Month/order=data;
format Month $months.;
var pay ;
table pay=' '* Month=' ' ;
run;

The options on Proc sort should sort the month by numeric value not character order.

Then the option Order=data on the class says use the values as they appear in the data set.

Or perhaps: Formats are typically stored in a sort order (which is different than the result you want). The NOTSORTED makes them appear in order in the stored format as assigned. Then the interaction of order=data and Preloadfmt places the result in stored order. Note that preloadfmt will also produce values in the report that may not have occurred in the data if you use the Printmiss option.

proc format out=fmt;
value $months (notsorted)
1='07'
2='08'
3='09'
4='10'
5='11'
6='12'
7='01'
8='02'
9='03'
10='04'
11='05'
12='06';
run;

Proc tabulate data =work.pt format=dollar12.;
class  Month/order=data preloadfmt;
format Month $months.;
var pay ;
table pay=' '* Month=' '/printmiss ;
run;

[Whitlea]

Both of these suggestions gave me the correct order in months on my table but they did not reorder the data. So now my table has 07-06 in months on top, but the pay data order is months 1-12 (July's pay is now January's pay). I have tried changing the order= option and different sorting options but have not been successful in reordering the data.  Do you have any other suggestions? Thank you!

[Patrick]

Try if option order=formatted helps.

[ballardw]

---

@Patrick wrote:

Try if option order=formatted helps.

---

Almost certainly will have 01 02 03 ... not the 07 08 ...      05 06 OP requests.

[PaigeMiller]

By using actual numeric values (rather than character values) that are valid SAS date values, then TABULATE (and most other procedures) will put things in the desired order.

Actual valid SAS date values are integers that reflect the number of days since 01JAN1960.

So, if your months are '01JUL2022'd through '01JUN2023'd (the year chosen, 2022 and 2023 is irrelevant, you can pick any year) then TABULATE will order these in the desired order without much effort at all, and you can format them any way you want (such as JUL2022, or 202207 or 07 or ...). You would need to use the option ORDER=UNFORMATTED in TABULATE.

Any time you turn calendar information into character strings, you will have this problem. The solution is to ALWAYS (that's 100% of the time, no exceptions) work with calendar information as valid SAS date values.