topic Re: proc format - Use a table to make it more dynamic in SAS Programming

proc format - Use a table to make it more dynamic

TBS1 — Fri, 04 Aug 2023 20:38:08 GMT

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!

Re: proc format - Use a table to make it more dynamic

PaigeMiller — Fri, 04 Aug 2023 20:44:44 GMT

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

Re: proc format - Use a table to make it more dynamic

ballardw — Fri, 04 Aug 2023 21:53:05 GMT

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.

Re: proc format - Use a table to make it more dynamic

TBS1 — Tue, 15 Aug 2023 17:26:28 GMT

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!

Re: proc format - Use a table to make it more dynamic

PaigeMiller — Tue, 15 Aug 2023 17:30:40 GMT

@TBS1

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

Re: proc format - Use a table to make it more dynamic

Reeza — Tue, 15 Aug 2023 17:55:44 GMT

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;

Re: proc format - Use a table to make it more dynamic

TBS1 — Tue, 15 Aug 2023 19:10:56 GMT

@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!

Re: proc format - Use a table to make it more dynamic

Tom — Tue, 15 Aug 2023 20:52:12 GMT

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                                    |
----------------------------------------------------------------------------