proc format - Use a table to make it more dynamic

[TBS1]

Hi!

I would like to use the proc format in a more dynamic way.

Instead of 

proc format;
    value points_range
        25-high='High'
        15-<25='Medium'
        other ='Low';
run;

I would like to reference a table I created earlier to make it more dynamic. There are two variables in the table: One is a comparison value and the second contains the desired assignment value.

Something like

proc format;
    value points_range
        $referenceTable1.var1 = $referenceTabl1.var2
run; 

Is this possible? Is *this* the way to go? Is there a better way?

Does anyone have a hint for me?

Thanks in advance!

[PaigeMiller]

In this tiny example, making it dynamic doesn't really buy you much. For larger situations, you can make PROC FORMAT more dynamic by creating formats from a SAS data set. Example: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/proc/n1e19y6lrektafn1kj6nbvhus59w.htm

[ballardw]

SAS can use a data set to make formats as @PaigeMiller indicates.  The option is CNTLIN=datasetname

There are some caveats:

The variables used by proc format to create have specific names and you must use them.

Proc format will also make a data set that can be used to document the formats or to be used. You may want to look at such using a format or two that you have written to see the details.

proc format cntlout=fmtout;
    value points_range
        25-high='High'
        15-<25='Medium'
        other ='Low';
run;

Basically you provide the name of the format, the type, 'C' for character, 'N' for numeric, start for the beginning of a range, End for the end of the range and Label for the format displayed values.

When you are using options such as Other you will want to look at the HLO variable. that variable is used to indicate if the range includes H (high), L (low) or O (other).  If your ranges exclude one of the end points then you look at the variables SEXCL (Y or N for start exclude ) and EEXCL(Y or N for end exclude ). Your <25 is an EEXCL=Y.

If you get into any of the more complex formats I suggest making small examples like this (don't us a lot of ranges) and create the CNTLOUT set and examine each observation carefully.

For Formats with an OTHER  do not actually have Start and End values supplied by you, that will display with the HLO=O set.

[TBS1]

Thank you @ballardw and @PaigeMiller!

Your contributions have led me in the right direction, though they have not entirely answered my cause. Thanks anyway, they really helped me a lot!

[PaigeMiller]

@TBS1 

More questions? That's what we're here for — well, I can't speak for @ballardw — but ask away!

[TBS1]

@PaigeMiller 

Thank you very much! 😊

Thank you for the offer! 🙂 May I then perhaps refer to another question? I'm not sure if there might not be a better approach than the one I have thought of: https://communities.sas.com/t5/SAS-Programming/Excel-export-with-format/m-p/889420#M351405

Thanks in advance!

[Reeza]

One trick is to use CNTLOUT to see the data set that SAS creates when you create a format. 

Then you can see how you need to structure your data set though not all the details are usually required. Here's an example:

*fake data to work with;
data sample;
    do x=1 to 30;
        y=x;
        output;
    end;
run;

*create format;
proc format;
    value points_range 25-high='High' 15-<25='Medium' other='Low';
run;

*export format to dataset;
proc format cntlout=want;
    select points_range;
run;

*view format;
proc print data=want;
    var start end label fmtname type hlo;
run;

*create new dataset from minimal variables;
data example;
    set want;
    fmtname='points_range_ex';
run;

*create format with new name;
proc format cntlin=example;
run;

*display sample data with formats to check output;
proc print data=sample;
    var x y;
    format x points_range. y points_range_ex.;
run;

[Tom]

To make this format.

proc format;
    value points_range
        25-high='High'
        15-<25='Medium'
        other ='Low';
run;

Your dataset needs at a minimum two numbers and three strings.   But to make the ranges you need both the start and the end values.  Since it is easier to remember then to predict the future let's assume the source data looks like:

data ranges;
  input cutpoint label $40.;
cards;
25 High
15 Medium
.  Low
;

Since your format is equivalent to this one:

value xx
 low-<15 = 'Low'
 15-<25 = 'Medium'
 25-high = 'High'
 ;

We can generate a CNTLIN type dataset to define such a format from the cut points using code like:

data formats;
  set ranges  end=eof;
  fmtname='points_range';
  start=cutpoint;
  end=lag(cutpoint);
  sexcl='N';
  eexcl='Y';
  if missing(start) then hlo='L';
  if missing(end) then do; 
    hlo='H';
    eexcl='N';
  end;
run;

proc format cntlin=formats ;
run;

----------------------------------------------------------------------------
|                   FORMAT NAME: POINTS_RANGE LENGTH: 6                    |
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH:   6  FUZZ: STD       |
|--------------------------------------------------------------------------|
|START           |END             |LABEL  (VER. 9.4     15AUG2023:16:48:34)|
|----------------+----------------+----------------------------------------|
|LOW             |              15<Low                                     |
|              15|              25<Medium                                  |
|              25|HIGH            |High                                    |
----------------------------------------------------------------------------


----------------------------------------------------------------------------
|       FORMAT NAME: XX       LENGTH:    6   NUMBER OF VALUES:    3        |
|   MIN LENGTH:   1  MAX LENGTH:  40  DEFAULT LENGTH:   6  FUZZ: STD       |
|--------------------------------------------------------------------------|
|START           |END             |LABEL  (VER. V7|V8   15AUG2023:16:48:34)|
|----------------+----------------+----------------------------------------|
|LOW             |              15<Low                                     |
|              15|              25<Medium                                  |
|              25|HIGH            |High                                    |
----------------------------------------------------------------------------