Dynamic proc format

[pangea17]

Hello everyone,

I am trying to create a dynamic proc format that is based on the monthly data for the past 7 or 8 years.   I am using SAS version SAS 9.4 and enterprise guide 8.3.  Here is a look at a portion of the normal proc format that I am trying to emulate.

proc format;
INVALUE classordinal
'2016-05'= 1
'2016-06'= 2
'2016-07'= 3
'2016-08'= 4
'2016-09'= 5
'2016-10'= 6
'2016-11'= 7
'2016-12'= 8
'2017-01'= 9
'2017-02'= 10
'2017-03'= 11
'2017-04'= 12

;

RUN;

I figured out how to generate the needed months, but don't know how to put it into a proc format statement.

data empty;
format vintage yymmd7.;
do i = 96 by -1 to 1;
vintage = intnx('month', today(), -i, 'B');
classrank = 97 - i;
output;
end;
drop i;
run;

proc sql;
select put(vintage, yymmd7.), put(classrank, 8.)
into :vintages separated by ' ', :classranks separated by ' '
from empty;
quit;

results

2016-05	1
2016-06	2
2016-07	3
2016-08	4
2016-09	5
2016-10	6
2016-11	7
2016-12	8
2017-01	9
2017-02	10
2017-03	11
2017-04	12

Thanks for your help.

[PaigeMiller]

May I try to save you some work?

Turning months like '2016-05' into the number 1 seems the exact opposite of what formats and informats were supposed to do. Formats and informats are used to make things more understandable, not less understandable. '2016-05' is much more understandable than 1.

You have a meaningful month that anyone understands, you really ought to be converting these to valid SAS dates so that all the functions in SAS are available, so character string '2016-05' ought to be converted to numeric version of this date. Then it is easy using the INTNX function to determine how many months have elapsed, and so on. No need to type out the very long PROC FORMAT you would need.

To turn '2016-05' into something that SAS understands, a valid SAS date value, use this:

data a;
     txt='2015-06';
     n=input(compress(txt,'-'),yymmn6.);
     format n yymmn6.;
run;

[pangea17]

Thanks for the response.  I am not trying to calculate the number of months that have elapsed.  I am trying to sort records based on the month and year (vintage) they happened.  I create a variable vintage $7.  vintage = CATS(RYear,'-',RMonth);  where RMonth = month(cycledate) Ryear=year(cycledate)

I created another variable called classrank   classrank=input(vintage,classordinal.) which gives each vintage a number assigned by the proc format invalue statement. 

proc format;
INVALUE classordinal
'2016-05'= 1
'2016-06'= 2
'2016-07'= 3

;

RUN;

This way I can summarize data by year and month and sort them by a numeric value, since SAS would sort 2016-10 prior to 2016-5.  

[Reeza]

Yes, but it will sort 2016-05 before 2016-10 correctly. If you have two digit months you'll have a correct sort.

[pangea17]

The month that is created doesn't have 2 digits which is an issue. SAS only brings back 1 or 2 instead of 01 , 02. with the month function.

[Tom]

Just tell SAS to include the leading zero.  Assuming your month variable is numeric use the Z2. format to generate a two digit string.

vintage = CATS(RYear,'-',put(RMonth,Z2.)); 

Or better still just generate the string directly from the DATE variable you say you have.

vintage = put(cycledate,yymmd7.);

[pangea17]

perfect! thanks

[PaigeMiller]

---

@pangea17 wrote:

Thanks for the response.  I am not trying to calculate the number of months that have elapsed.  I am trying to sort records based on the month and year (vintage) they happened.  I create a variable vintage $7.  vintage = CATS(RYear,'-',RMonth);  where RMonth =

---

Your formats and informats that you are trying to create are not needed to order the months properly.

Having an actual SAS date gives you the ability to order the months. 

vintage = mdy(rmonth,1,ryear);
format vintage yymmd7.;

In the above code, the FORMAT is optional if all you want to do is sort months.

Also, if your goal is to sort months, that should be mentioned in the first post, but it hasn't been mentioned until you just mentioned it. It is always good to mention the goal, rather than just jumping into the technical problem of formats and informats.

[ballardw · Accepted solution]

You had most of the parts needed.

data empty;
   fmtname = 'Classordinal';
   type='I';
   do i = 96 by -1 to 1;
      start = put( intnx('month', today(), -i, 'B'),yymmd7.);
      label = 97 - i;
      output;
   end;
   drop i;
run;

proc format library=work cntlin=empty;
run;

Proc Format can use a data set with specific variables with the CNTLIN (control in) data set option.

You need to provide the name of the format in the variable FMTNAME, they TYPE (C=character N=numeric I=Numeric Informat).

START is the start of a value range and all that is needed for single value, then LABEL has the target text.

You want to make sure the value for START is what will be looked up.

There are other bits that come into play. You can use Proc Format to create an output data set describing the format using the CNTLOUT= <dataset> option to see what is needed to use other options such as ranges or the OTHER option.

[Tom]

Formats convert values into text. Informats convert text into values.

If you want to make the string '1','2','3', etc you could use a FORMAT.

If you want to make the numbers 1,2,3 etc you need to use an INFORMAT.

data format;
  retain fmtname 'month_to_number' type 'I';
  do label=1 to 12 ;
    start = put(intnx('month','01APR2016'd,label),yymmd7.);
    output;
  end;
run;
proc format cntlin=format fmtlib; 
run;

Result:

----------------------------------------------------------------------------
|                INFORMAT NAME: @MONTH_TO_NUMBER LENGTH: 7                 |
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH:   7  FUZZ:        0  |
|--------------------------------------------------------------------------|
|START           |END             |INVALUE(VER. 9.4     08MAY2024:14:39:11)|
|----------------+----------------+----------------------------------------|
|2016-05         |2016-05         |                                       1|
|2016-06         |2016-06         |                                       2|
|2016-07         |2016-07         |                                       3|
|2016-08         |2016-08         |                                       4|
|2016-09         |2016-09         |                                       5|
|2016-10         |2016-10         |                                       6|
|2016-11         |2016-11         |                                       7|
|2016-12         |2016-12         |                                       8|
|2017-01         |2017-01         |                                       9|
|2017-02         |2017-02         |                                      10|
|2017-03         |2017-03         |                                      11|
|2017-04         |2017-04         |                                      12|
----------------------------------------------------------------------------

Which you can then use with INPUT().

new_var = input(old_var,month_to_number.);

If you want to count the number of months since '01APR2016'd there is no need for either an informat or a format.  Just use the INTCK() function.

new_var=intck('month','01APR2016'd,input(cats(old_var,'-01'),yymmdd10.));